PHP从mysql表中获取最大数字

Question

我已经统计了投票给某个候选人的选民数量，我想显示哪一个获得了最高的投票数。我试图将它们存储在变量中，这样我就可以使用max()方法，但是我得到了错误"undefined ".any帮助

  <?php 
 $query="select count(*) as total from voting Where ca_id=1";
  //ca_id is the candidate id that voter choose
 $result = mysql_query($query);
 $row = mysql_fetch_assoc($result);
 echo"$row[total]<br>";


 $query="select count(*) as total2 from voting Where ca_id=2";
 $result = mysql_query($query);
 $row2 = mysql_fetch_assoc($result);
 echo"$row2[total2]<br>";

 $query="select count(*) as total3 from voting Where ca_id=3";
 $result = mysql_query($query);
 $row3 = mysql_fetch_assoc($result);
 echo"$row3[total3]<br>";

 $query="select count(*) as total4 from voting Where ca_id=5";
 $result = mysql_query($query);
 $row4 = mysql_fetch_assoc($result);
 echo"$row4[total4]<br>";



?>

Answer 1

SELECT count(1) co, ca_id FROM voting GROUP BY ca_id ORDER BY co DESC LIMIT 5

Answer 2

您不需要为此执行四个查询。您可以只使用一个查询。在您的情况下，您可以这样做：

select ca_id, count(*) as counter from voting group by ca_id order by counter desc

你可以通过一个查询得到你的结果

如前所述，在这种情况下，对于与数据库相关的调用，PDO是更好的选择

Answer 3

Please, don't use mysql_* functions in new code.不再维护它们，deprecation process已经开始对其进行维护。看到red box了吗？相反，学习prepared statements，并使用PDO或MySQLi -此article将帮助您决定哪一个。

您可以使用使用MySQLi扩展的类似内容。

<?php

$mysqli = new mysqli('host', 'user', 'pass', 'db');

$sql   = "SELECT COUNT(votes) as vote_count, ca_id as candidate_id FROM voting GROUP BY ca_id ORDER BY vote_count DESC";
$result= $mysqli -> query($sql);

// Error Checking
if($mysqli -> error){
   echo 'Error: '.$mysqli -> error;
   exit;
}

while($row = $result -> fetch_object()){

   // Print out each candidate ID and the amount of votes they had.
   echo 'Candidate ID: '.$row -> candidate_id.', Votes: '.$row -> vote_count.'<br/>';

   // If you want to just show the highest voted candidate - put the data in an array
   $votes[$row -> vote_count] = $row -> candidate_id;

}

echo max(array_keys($votes));

这也会将你运行的查询量减少到只有1。