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 stackoverflow.com/questions/3126972/mysql-row-number — Tomasz Madeyski
– Tomasz Madeyski, Commented Dec 2, 2013 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
– Ahiru Nakamura, Commented Dec 2, 2013 at 21:31

Mosty Mostacho · Accepted Answer · 2013-12-02 20:40:12Z

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 2, 2013 at 20:40

answered Dec 2, 2013 at 20:32

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ahiru Nakamura Over a year ago

gonna test it after grub! thanks! btw, how do I count it directly in PHP?

Mosty Mostacho Over a year ago

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 :)

Ahiru Nakamura Over a year ago

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!)

Mosty Mostacho Over a year ago

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 :)

Ahiru Nakamura Over a year ago

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!!

Dave · Accepted Answer · 2013-12-02 20:22:21Z

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 2, 2013 at 20:22

Dave

3,6581 gold badge18 silver badges9 bronze badges

1 Comment

Ahiru Nakamura Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related