Basic ranking of MySQL data and printing result with php

Question

I have a table which in which I want to rank rows where one of the columns equals a value I have defined.

For example in this table I want to get all rows where Col1 = a, then find what is the rank of the row in which Col3 = Ross (ranked by score in Col2).

Col1 | Col2 | Col3
------------------
a    | 10   | John
a    | 6    | Nick
a    | 8    | Ross
a    | 2    | Tim
a    | 4    | Paul
b    | 9    | John
b    | 3    | Nick
b    | 5    | Ross
b    | 7    | Tim
b    | 1    | Paul

Ultimately I want to calculate this:

Col1 | Col2 | Col3 | Rank
-------------------------
a    | 10   | John | 1
a    | 8    | Ross | 2
a    | 6    | Nick | 3
a    | 4    | Paul | 4
a    | 2    | Tim  | 5

And print the result "2".

I can sort the table with this query, but dont; know how to then print the result I need (with php).

$query = "SELECT * FROM exampleTable WHERE Col1 = a order by Col2 DESC";

Do you have a primary key that you are not showing here? Or do you want to sort by row identifier? — Asad Saeeduddin, Oct 23 '12 at 19:13

Asad Saeeduddin · Accepted Answer · 2012-10-23T19:32:43.483

1

Try this:

"SET @rank=0; SELECT * FROM (SELECT *, @rank:=@rank+1 AS Rank FROM exampleTable WHERE Col1 = 'a' order by Col2 DESC) AS t"

Here is a test run that shows it working.

If you want to find Ross's rank you can run the query with WHERE Col3 = 'Ross' added and only the rank column selected, as demonstrated here

edited Oct 23 '12 at 19:32

answered Oct 23 '12 at 19:18

Asad Saeeduddin

46,193
6
90
139

That's exactly the query I need - how do I print the result with PHP? – ajcw Oct 23 '12 at 20:17
First establish a connection using `mysqli_connect`, then use `mysqli_query(etc.)` to query and retrieve a resource, then use its fetch array method to return the result set as an array. http://www.phphaven.com/article.php?id=65 is a short tutorial on using mysqli. – Asad Saeeduddin Oct 23 '12 at 20:28
I am getting an 'error in the SQL syntax'. I think it is because of the semi-colon in the query, but I can't figure out how to delimit it. Any suggestions? – ajcw Oct 24 '12 at 08:31
@JohnCatterfeld It should also say where the error is occurring. `error in the SQL syntax near ...` – Asad Saeeduddin Oct 24 '12 at 08:34
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Rank FROM (SELECT *, @rank:=@rank+1 AS Rank FROM exampleTable WHERE Col1 ' at line 119 – ajcw Oct 24 '12 at 08:35
@JohnCatterfeld Try using mysqli_multi_query instead. Same syntax. – Asad Saeeduddin Oct 24 '12 at 08:38
Thanks for your help - this what I got to work (table and columns have different names) `select * from (select @rownum:=@rownum+1 Rank, p.* from events p, (SELECT @rownum:=0) r WHERE SportID = 1 order by TotalPts desc) Rank_events where CompetitorID = 5` – ajcw Oct 25 '12 at 13:11
This solution is deprecated. You can use the RANK() function now (since 8.0) (see https://stackoverflow.com/a/59883677/1557649 ) – movAX13h Jan 23 '20 at 17:03

score 1 · Answer 2 · edited May 23 '17 at 10:24

with pdo

first create pdo connection by

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
       'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, 
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

than use like that

$query= $pdo->prepare("SELECT * FROM exampleTable WHERE Col1 = a order by Col2 DESC");

$query->execute();
$article=$query->fetchAll(pdo::FETCH_ASSOC);

reason i am using the fetchAll not the loop is its faster if memory does matter than use the loop

PDO::fetchAll vs. PDO::fetch in a loop

score 0 · Answer 3 · answered Jan 23 '20 at 17:00

0

To get the ranking of results, you can use the MySQL RANK() function since MySQL 8.0.

Your query then looks like this:

$query = "SELECT *, RANK() OVER (ORDER BY Col2 DESC) myRank FROM exampleTable WHERE Col1 = 'a'";

answered Jan 23 '20 at 17:00

movAX13h

608
6
9

Basic ranking of MySQL data and printing result with php

3 Answers3