Setting a Value by assuming column in a Table MySQL/PHP

Question

Hey
i am having a table with 8 column and following values
1 2 3 4 5 6 7 8
0 1 0 0 0 0 0 0
i need a query which will analyze the no of 1's and 0s in the column and if no of 0 is
greater than 4 then it should print failed else pass,how can i do that? either by MYSQL or PHP?

Answer 1

Might not be very neat, but you can count the number of 0s with a query such as the following (assuming your columns are named 1, 2, etc:

SELECT IF(`1` = 0, 1, 0) +
       IF(`2` = 0, 1, 0) +
       IF(`3` = 0, 1, 0) +
       IF(`4` = 0, 1, 0) +
       IF(`5` = 0, 1, 0) +
       IF(`6` = 0, 1, 0) +
       IF(`7` = 0, 1, 0) +
       IF(`8` = 0, 1, 0) NoOfZeros
FROM   your_table;

Test case:

CREATE TABLE your_table (`1` int, `2` int, `3` int, `4` int, 
                         `5` int, `6` int, `7` int, `8` int);

INSERT INTO your_table VALUES (1, 0, 1, 0, 1, 0, 1, 0);

Result:

+-----------+
| NoOfZeros |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

You can run this query from PHP, and display the error message you require when the NoOfZeros field in the result set is greater than 4.

Answer 2

SELECT CASE WHEN `1`+`2`+`3`+`4`+`5`+`6`+`7`+`8` < 4 THEN 'Failed' ELSE 'Passed' END
FROM test_table;