CODEWITHSUNDEEP
phpmysqldatabasecount
Martin L
Martin L

Reputation: 1

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 row 3 by counting all the rows from the start of the database to the row I am after.

Upvotes: 0

Views: 509

Answers (3)

Andrej
Andrej

Reputation: 7504

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);
}

Upvotes: 1

tadman
tadman

Reputation: 211590

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.

Upvotes: 0

piotrp
piotrp

Reputation: 3864

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.

Upvotes: 0

Related Questions