CODEWITHSUNDEEP
phpmysqlranking
ajcw
ajcw

Reputation: 23804

Basic ranking of MySQL data and printing result with php

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

Upvotes: 0

Views: 2425

Answers (3)

movAX13h
movAX13h

Reputation: 666

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

Upvotes: 0

user1726343
user1726343

Reputation:

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

Upvotes: 1

NullPoiиteя
NullPoiиteя

Reputation: 57322

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

Upvotes: 1

Related Questions