PHP - position in table

Question

How can I find the position a certain row has in my database table?

In my database I have a bunch of Players from a game. They have columns such as ID, name, level, world, and so on.

I want to sort them by Level, and see what position they are in in the list.

So far I've tried this, but it only prints out 0 (the start value I put). It does not seem to iterate through.

Previously I made sure the name is stored in $name.

$query = "SELECT * FROM rookstayers ORDER BY level DESC";

$globalPos = 0;
$result = mysql_query($query);
$num = mysql_numrows($result);
$i = 0;
while($i < $num) {
    $posName = mysql_result($result, $i, 'name');
    if($posName == '$name')
    {
        $globalPos = $i;
        break;
    }

    $i++;
}

If my table looks like this (after sorting it by level):

name - level
Joe - 50
Jacob - 47
Sarah - 34
Anna - 19

Then "Sarah" would be number 3. Anna number 4, etc...

I want the position-number to be in $globalPos.

I only found pure SQL code for this, but I want it in PHP. Is it not possible to do in PHP?

possible duplicate of [Rank function in MySQL](http://stackoverflow.com/questions/3333665/rank-function-in-mysql) — kero, May 24 '15 at 20:02
yes but that's sql, i dont know much about it. i found nothing in php — TyhaiMahy, May 24 '15 at 20:03
http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by — fbas, May 24 '15 at 20:13
does it return 0 for any name? cause you receive 0 for Joe unless you change `$globalPos = $i;` to `$globalPos = $i+1;` — h3n, May 24 '15 at 20:44

score 0 · Answer 1 · answered May 24 '15 at 21:25

I'm not sure if this is the only problem:

If you use single quotes ', the string is not parsed for variables. If you want to compare a string stored in $name with a string stored in $posName, you should use $posName === $name. You could also use $posName === "$name", but the double quotes are unnecessary.

Notice also, that the mysql functions are deprecated. You should use mysqli or PDO_MySQL instead. (See Choosing an API)

score 0 · Answer 2 · answered May 25 '15 at 00:37

<?php

/*

CREATE TABLE results
(name VARCHAR(12) NOT NULL PRIMARY KEY
,score INT NOT NULL
);

INSERT INTO results VALUES
('Anna',19),
('Jacob',47),
('Joe',50),
('Sarah',34);

*/

include('path/to/connection/stateme.nts');

$query = "

SELECT name, score FROM results ORDER BY score DESC;

";

$result = mysqli_query($conn,$query);

$i=1;

while($row = mysqli_fetch_assoc($result)){

echo $i." ".$row['name']." ".$row['score']."<br/>\n";
$i++;
};

?>

PHP - position in table

2 Answers2