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

开发者_运维知识库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?

---

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

---

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.