Check if all selected values in the database are the same or not

Question

I have this code where am trying to check if all values in the selected rows are the same or not and its just not working

$name = $row['name'];

$sql = "SELECT status FROM testing WHERE name='$name'";
$result = mysqli_query($conn, $sql);

$datas = array();

if(mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        $datas[] = $row;
    }
}

if (count(array_unique($datas)) === 1 && end($datas) === 'No') {
    echo 'all the same';
}
else {
    echo 'not all the same';
}

after inserting the values into an array $datas and still trying to check if the value No is the same in all the selected row in the database but it keeps throwing an error

NOTICE: ARRAY TO STRING CONVERSION

Edit: I added this $datas[] = $row['status']; and it worked

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 04 '20 at 21:15

score 0 · Answer 1 · answered Sep 04 '20 at 21:13

0

Why not simply do this in the database?

SELECT COUNT(DISTINCT status) < 2 as all_the_same 
FROM testing 
WHERE name = ?

This query gives you a scalar resultset (one line and one column, called all_the_same): if all values are the same you get 1, else 0.

If there are no rows at all for the given name, then you get 0 too. We can differentiate this case by returning null instead:

SELECT CASE COUNT(DISTINCT status)
    WHEN 0 THEN NULL
    WHEN 1 THEN 1
    ELSE 0
END as all_the_same
FROM testing
WHERE name = ?

answered Sep 04 '20 at 21:13

GMB

216,147
25
84
135

but what am trying to do is that if all the database values is 'No' then do this but if it is "Yes" or "Yes and No" then do differently. cause if i do it your way then it means that if all of it is "Yes" then do the same that you will do if all is still "No" – GrandFenrir Sep 04 '20 at 21:21
@GrandFenrir: the query returns `1` for "Yes" and `0` for "No". No additional processing is needed on application side. Just run the query and fetch the result. – GMB Sep 04 '20 at 22:02

Check if all selected values in the database are the same or not

1 Answers1