查看数组并从重复值中计算 true 或 false

Question

我在 SQL 中有一个表，用户可以在其中多次回答（相同用户和不同用户）。我想计算有多少个真值或假值。

例如，用户 1 在我的表中有 5 行，其中 3 次为 true，2 次为 false，而用户 9 则有 10 次 true 和 1 次 false，但我不知道用户编号是多少。

我想要像

User 1 - 5x True 1x False、User 4 1x True 3x False 等输出。但我不知道哪些用户和用户列表可以增长。

Answer 1

有一个使用循环的简单（不推荐）解决方案：

$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']]++;
}

您将有一个数组保存每个用户的肯定答案和每个用户的总答案，然后您可以使用它来计算否定答案

推荐的解决方案是使用 group by sql 语句，该语句给出你所有的计算信息。

$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

请参阅： http://dev.mysql.com/tech-resources/articles /wizard/page3.html

Answer 2

对于这个问题最优雅的解决方案是实际上有两个 SQL 表；每个用户（用户 ID、用户名等）一行一行，每个投票一行，每个用户可以有多个投票。

以下示例将回显有关数据的一些信息。

<?php
$sqlusers = mysql_query("SELECT userid FROM user_table")//This line grabs all users from the database.
$users = mysql_fetch_array($sqlusers);//This line creates an array containing all users.
foreach($users as $key=>$currentuser){
   $sqlvotes = mysql_query("SELECT userid, vote FROM vote_table WHERE userid = $currentuser[userid]");
   $votes = mysql_fetch_array($sqlvotes);//obtain an array of votes the current user has submitted
  $votefrequency = array_count_values($votes)//counts the amount of trues and falses in the $votes array, and returns an array with the [true]  and [false] indexes containing their respective frequency.
  echo "user ".$userid." has voted ".$votefrequency[true]." times true and ".$votefrequency[false]." times false/n";
  echo "average vote:". (($votefrequency[true] - $votefrequency[false] > 0) ? "true" : "false" );
}