finding the number of a selected row

sol01

Ok say I grab a row out of my database.....is there a way of finding out what number it is?

Database's ID is autoincremented/primarykey

say I have the following rows

ID

1
2
3
4
5
6
9
15

say I grab 9, it does not mean that 9 is the 9th entry. It's actually the 7th entry. How can I find out what the entry number is?

Anon

You can do something like:

select count(ID) where ID < 9

that will tell you how many rows there are before the one you're looking for, you'd just have to add one to the result.

bitbit

Of course if you do:

select count(ID) where ID <= 9

then you don't have to add one to it anymore.

sol01

So I would just do this:

$count = mysql_query("select count(ID) where ID <= 9");
print $count;

and that should do it?

bitbit

It would be more like:

$count_query = mysql_query("select count(ID) where ID <= $ID");

list($count) = mysql_fetch_row($count_query);

print $count;

$ID would be the ID, in this case 9.

sol01

So then how does that count by?? What order desc/asc?? It seams to be doing it ordered by ASC.....how can I make it count in a DESC manner?

[deleted]

The point is; rows in a database do not have a number, because their 'number' depends on what you order it by.

SELECT name FROM users ORDER BY name ASC;

would bean that 'aaa' is record #1, but when you do

SELECT name FROM users ORDER BY name DESC;

that same 'aaa' record is now the last record.

You should add the 'number' while you are printing the results, but that is the only place where that number makes sense.

After running the a query, the first result you get is record #1, the second is #2 and so on.

If you have a query like
SELECT name FROM users WHERE name='bbb';
then you can only find out what the record number is by building a query to find out how many records you would have printed before 'bbb' if you would print all records.

something like
SELECT count(*) FROM users WHERE name < 'bbb' ORDER BY name ASC;

But, whenever a records is inserted or deleted, this numebr changes immediately, so you cannot use the number for anythjing other than an indication of how many records you have.