I have a table in SQL, where users can answer many times (the same users and different users). I want to count how many true or false values there are.
For example, like user 1 has 5 rows in my table, 3 times true, and 2 times false and user 9 has got 10 true and 1 false like that but I would not know what user numbers.
I would like output like
User 1 – 5x True 1x False, User 4 1x True 3x False etc. But I would not know what user and the user list can grow.
Advertisement
Answer
there is a simple (not recommended) solution using a loop:
$resultq = mysql_query('select value, user_id from answers');
$answers_per_user = array(); // positive answers per user
$totals_per_user = array(); // total answers per user
while($result = mysql_fetch_assoc($resultq)){
if($result['answer'])
$answers_per_user[$result['user_id']] += $result['answer']; // increment positive answer counter for user
$totals_per_user[$result['user_id']]++;
}
you would have an array holding positive answers per user and total answers per users which you can then use to calculate negative answers
the recommended solution is to use a group by sql statement that gives you all the calculated information.
$result = mysql_query('select sum(value) as positivecount, count(*) as total, user_id from answers group by user_id');
while($data = mysql_fetch_assoc($result)){
// $data will hold positivecount,total, and user_id giving you all the data you need for calculating negative answer values.
}
// alternatively, use a query like this for counting the answers that were 'beans':
// select sum(if(value = "beans", 1, 0)) as answered_beans, count(*) as total, user_id from answers group by user_id
see: http://dev.mysql.com/tech-resources/articles/wizard/page3.html