PHP get largest number from mysql table

Question

i have counted the number of voters that voted for certain candidate i want to display which one got the highest voting number. i tried to store them in variables so i can use max() method but i got the error "undefined ".any help please

  <?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>";



?>

[Please, don't use `mysql_*` functions in new code](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — John Conde, May 21 '13 at 14:06
It would be easier for us to help you if you set up a [SQL Fiddle](http://sqlfiddle.com/) — John Conde, May 21 '13 at 14:07

score 3 · Answer 1 · answered May 21 '13 at 14:11

3

You don't need to perform four queries for this. You could just use a single query. In your case, you could do:

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

And you can get your result with a single query

As mentioned, PDO is a better alternative in this case for your database-related calls

answered May 21 '13 at 14:11

Filippos Karapetis

4,367
21
39

i have different IDs and they are repeated more than once so i want to count number of duplication for each id – Suma May 21 '13 at 14:46

score 2 · Answer 2 · answered May 21 '13 at 14:10

2

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

answered May 21 '13 at 14:10

Your Common Sense

156,878
40
214
345

score 2 · Answer 3 · edited May 23 '17 at 10:25

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which.

You could use something like which uses the MySQLi extension.

<?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));

This will also cut the amount of queries your running down to just 1.

i got this error Fatal error: Call to a member function fetch_object() on a non-object — Suma, May 21 '13 at 14:28
Have you changed the DB connection settings so they connect to your DB? Also, i've added a few lines that will print out any error message. — ajtrichards, May 21 '13 at 14:36

score 0 · Answer 4 · answered May 21 '13 at 14:13

0

first of all use group by to retrieve all data in one query, instead of querying for each of the candidates:

SELECT ca_id, count(*) as total FROM voting GROUP BY ca_id

if you need a top voted id use:

SELECT ca_id, count(*) as total FROM voting GROUP BY ca_id SORT BY total 
DESC LIMIT 1

Note: not the most efficient solution but will be way faster then querying for each ca_id.

answered May 21 '13 at 14:13

Headshota

21,021
11
61
82

i used this one "SELECT ca_id, count(*) as total FROM voting GROUP BY ca_id" that you gave me but it didn't give the total voting for each candidate"i have 5 candidate". – Suma May 21 '13 at 14:21

PHP get largest number from mysql table

4 Answers4