MYSQL row number

Is it possible to echo the row number of a MySQL table in PHP. Also is it then posible to use it with the:
mysql_query(SELECT * FROM table WHERE rownumber='3')
command?
Tanks alot.

My recommendation is to use an integer "id" for the primary key field in your table and then select the row by this id:


P

There are lots of tricks to do this sort of thing. For instance, what you might want is to have a "row number" in a select like this:

select * from table where cat=123;

1 fish
2 cat
3 dog

In this case mugane's suggestion won't work, since the pk will be something not necessarily in order for only parts of the table. Oracle does this row numbering thing automagically, by the way, but most databases don't do it automatically, and you have to do it yourself.

In a database like postgresql that supports sequences you can do this:

begin;
create temp sequence row_numbers;
select nextval('row_numbers'), * from table where...
end;

and the sequence will just disappear after you're done. since it's a temp sequence it would exist in only the name space of the current connection and other connections wouldn't see it and get weird results were they to do the same thing at the same time.

Not sure how to do it in MySQL though...

Thankyou every one for your answers. I will have a go at all of them an see which one I like the best.