Count rows in database and echo out desired row number

If I have 5 rows in my mysql database with ids of 2, 4, 5, 6 and 8 and I echo out id 5 how do I then echo out the row number which of course would be row 3? I get r开发者_高级运维ow 3 by counting all the rows from the start of the database to the row I am after.

Try to use

SELECT @row_num:=@row_num+1 as row_number, id from table inner join (select @row_num:=0) as temp

$pdo = new PDO(....);
$result = $pdo->query($sql)->fetchAll();
foreach($result as $row) {
    print_r($row);
}

The correct solution would be to add some auto_increment key to your table, then each row would have assigned a sequential number. Otherwise you are not guaranteed to get the same number for each id because SQL query without ORDER BY clause is not guaranteed to have any order (i.e. ids may be returned in random order).

Otherwise, the solution given by Andrej L is the correct one.

Couldn't you just count the number of rows before this in the sort order you're using?

COUNT(*) FROM table WHERE id < ?

In this case only rows with id 2 and 3 would count, so the result is 2. If you're using a 1-based index, add one to that and you have your answer.