CODEWITHSUNDEEP
phpmysqlsqlarraysrow
Ahiru Nakamura
Ahiru Nakamura

Reputation: 33

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

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…

Upvotes: 0

Views: 1054

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 1

Dave
Dave

Reputation: 3658

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

Upvotes: 0

Related Questions