How to retrieve row number from specific value in a mysql fetch row/array/assoc result via php?

Question

Supposedly I have this sql result in PHP ($myresults = mysql_fetch_… either assoc/row/array):

SELECT table1.name as name, sum(table2.numbers) as numbers FROM table2 INNER JOIN
 table1 ON table2.fk_id = table1.id GROUP BY name ORDER BY numbers DESC

---------------
| John | 800  |
---------------
| Mark | 500  |
---------------
| Bill | 300  |
---------------

So I am logged as Mark ($_SESSION['name'] == "Mark") and I want to know in which row # the value 'Mark' is located (in this case, row number 1, considering the first row is 0).

How to I get that via PHP?

Thanks…

EDIT: think of it as a High Score or Leaderboards table, I don't need the user id, but the row in which the user is located as of right now…

I guess you can find your answer here http://stackoverflow.com/questions/3126972/mysql-row-number — Tomasz Madeyski, Dec 02 '13 at 20:28
Although it does look like what Mosty Mostacho answered, it would give me the wrong answer (see my comment in his answer) like his 1st example. His 2nd example works perfectly. — Ahiru Nakamura, Dec 02 '13 at 21:31

Mosty Mostacho · Accepted Answer · 2013-12-02T20:40:12.667

1

You should use user defined variables this way:

SELECT table1.name as name, sum(table2.numbers) as numbers,
  @rank := @rank + 1 rank
FROM table2
CROSS JOIN (SELECT @rank := 0) init
JOIN table1 ON table2.fk_id = table1.id
GROUP BY name
ORDER BY numbers DESC

After a second thought, the group by might give you some trouble with the counting of the UDVs. This is another alternative but will be less performant than the previous approach.

SELECT *, @rank := @rank + 1 rank FROM (
    SELECT table1.name as name, sum(table2.numbers) as numbers
    FROM table2
    JOIN table1 ON table2.fk_id = table1.id
    GROUP BY name
) s
CROSS JOIN (SELECT @rank := 0) init
ORDER BY numbers DESC

Anyway, I would recommend counting directly in PHP. That will be more flexible and performant.

edited Dec 02 '13 at 20:40

answered Dec 02 '13 at 20:32

Mosty Mostacho

42,742
16
96
123

gonna test it after grub! thanks! btw, how do I count it directly in PHP? – Ahiru Nakamura Dec 02 '13 at 20:38
You most likely have some kind of loop to go through the results, right? fetch_assoc and that stuff? Then just add a `$counter` variable there. Initialize it in `0` outside the loop and then increment it inside the loop. There is your rank :) – Mosty Mostacho Dec 02 '13 at 20:41
I got the rank using a while loop, but to print the table as in "1 - John - 800… 2 - Mark - 500… " But what if I needed the "Your rank is # out of ###", the while loop with the $counter increment wouldn't apply here right? so I better off with the answer you provided? (I'm gonna test it right now!) – Ahiru Nakamura Dec 02 '13 at 21:12
There are different approaches to the issue you're talking about. The first and simplest would be to `select count(*)` the query so that you know the total in advance. Alternatively, if you're not using a `LIMIT` and you're actually getting all the results then I'm pretty sure there is a PHP function to count the number of results fetched by the query. Still I'd go for all these approaches before using UDVs :) – Mosty Mostacho Dec 02 '13 at 21:24
1

The 2nd approach in your answer sufficed what I needed… The 1st try would give me wrong row #s (due to the sum(), it was skipping rows, as if the row #s were reserved for the rows with same names with different numbers..) like 1, 5, 8… and not 1, 2, 3… Thanks!! – Ahiru Nakamura Dec 02 '13 at 21:30

score 0 · Answer 2 · answered Dec 02 '13 at 20:22

0

Modify your SQL to select primary ID along with the other data:

SELECT 
    table1.id as id,
    table1.name as name, 
    sum(table2.numbers) as numbers 
FROM 
    table2 
INNER JOIN
    table1 ON table2.fk_id = table1.id 
GROUP BY 
    name 
ORDER BY 
    numbers DESC

answered Dec 02 '13 at 20:22

Dave

3,658
1
16
9

that'd only bring the user id, but not exactly the row index… the numbers in table2 may vary so, today Mark might be in row #1, but tomorrow Mark might be on row #3 and so on… think of it like a videogame High Score table – Ahiru Nakamura Dec 02 '13 at 20:25

How to retrieve row number from specific value in a mysql fetch row/array/assoc result via php?

2 Answers2