CODEWITHSUNDEEP
phpmysql
user1781809
user1781809

Reputation: 11

Sorting and Ranking values of columns in mysql

Lets say I have 3 columns and 3 rows, the first column is for ID, the second is names, third is votes. like:

+----+------+-------+
| id | name | votes |
+----+------+-------+
|  1 | bob  |     7 |
|  2 | jill |     2 |
|  3 | jake |     9 |
+----+------+-------+

How can I have PHP compare the values in the votes field and sort it by whichever had the highest number, and attach a rank of #1,2,3, etc. depending on how many votes it had?

Each value will be displayed on a separate page. For example, if I went to 'bob's page' with the ID of 1, I would need it to display '#2 bob' since he would be ranked 2nd by votes.

Upvotes: 1

Views: 1391

Answers (4)

Prashant Singh
Prashant Singh

Reputation: 3793

You can make a separate column rank and update it by running the following code whenever your vote changes. This method will make you more efficient as in this you wont be sorting the table again and again when user visits his page: 

    $q = "select * from tableName order by votes DESC";
    $a = mysql_query($q);
    $count = 1;
    while($arr = mysql_fetch_array($a){
       $up = "update tableName set(rank) VALUES($count) WHERE name=$arr['name']";
       $aq = mysql_query($up);
       $count++;
    }

Now on individual pages, you can just retrieve the rank value and show

$user = "Bob";
$q = "select rank from tableName where name=$user";
$a = mysql_query($q);
$arr = mysql_fetch_array($a);
echo $arr[0];

Also this(a slight modification in other answer) should work for you :-

SELECT @rownum:=@rownum+1 AS rank, name, vote FROM table, (SELECT @rownum:=0) as P ORDER BY vote DESC

Upvotes: 1

GP cyborg
GP cyborg

Reputation: 198

You can use the MySQL 'ORDER BY' and display those ranks using PHP: For this example:

<?php
//connection
//DB selection
$query = "SELECT * FROM table_votes ORDER BY votes DESC";
$result = mysql_query($query);

for(int $i=1; $row = mysql_fetch_array($result);i++)
{
    echo "#".$i.$row['name']."<br/>";
}
?>

Upvotes: 0

didierc
didierc

Reputation: 14730

You want a SELECT query doing an ORDER BY votes, and create a special variable to handle the rank of a row:

SELECT @rownum:=@rownum+1 AS rank, name, vote FROM table, (SELECT @rownum:=0) ORDER BY vote DESC

This query should let you fetch an array with the rank, name, and number of votes of each person of your table.

Alternatively, you can just sort by vote, and add the rank value yourself with PHP.

Upvotes: 0

j.labra.escudero
j.labra.escudero

Reputation: 48

You could read the values returned by your query into an array and then sort said array.

Upvotes: 0

Related Questions