Tyler, > If I have a field in my DB that can have 4 different answers, lets say a,b,c > and d. How can I count the number of each in that field. So if there are 4 > a's 1 b 0 c's and 12 d's how can I get php to count this?
You must combine IF() with SUM(). Break it down into smaller problems: 1 getting the data out of the tbl and grab all of the answer rows 2 'count' the number of "a"s 3 do the same for the number of "b"s The first is trivial, but let's limit our attention to question 1 until we figure out how to word the query: SELECT questionNr FROM tblNm WHERE questionNr = 1 AND answer = 'a' Obviously this won't satisfy you - you don't want line after line of data, and it only works if the chosen answer is "a" and you read off the number of rows affected. What happens if the answer is "b". We'd have to execute the query all over again, but substituting "b" for "a". Is there another way? Yes, there is an IF() in SQL (manual: 6.3 Functions for Use in SELECT and WHERE Clauses). Let's try listing the question number and noting if the answer is "a" or "b" or...: SELECT questionNr, IF( answer = 'a', TRUE, FALSE ) AS Answer-A, IF( answer = 'b', TRUE, FALSE ) AS Answer-B, ... FROM tblNm WHERE questionNr = 1 Now we have a list of all of the answers to question 1, but instead of only "a" answers, we have a column of TRUEs and FALSEs for the "a" answers, and beside that a column of TRUEs and FALSEs for the "b"s, through "c" and "d". So beside the questionNr we have four columns telling us which data is relevant to count - if we simply count the TRUEs. So we've saved the repetitious executions, but the machine no longer does any useful counting for us. Substitute 1 for TRUE and 0 for FALSE, and we can have SQL do that last calc for us, ie SUM all of those "TRUE" values: SELECT questionNr, SUM( IF( answer = 'a', 1, 0 ) ) AS Answer-A, SUM( IF( answer = 'b', 1, 0 ) ) AS Answer-B, ... FROM tblNm WHERE questionNr = 1 Finally, if you get rid of the WHERE clause, and replace it with GROUP BY questionNr, it should work for every question in the test/survey. Regards, =dn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
