Page 1 of 2
Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 9:50 am
by aneuryzma
I'm using PHP+MYSQL.
How can I get the number of a row fetched from a table (for example, given a specific value) ?
thanks
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 11:40 am
by social_experiment
mysql_num_rows() or using COUNT()
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 11:59 am
by aneuryzma
I don't need the number of rows in my result set (mysql_num_rows() returns this). I need the row number in the original table (i.e. second row of the table).
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 12:44 pm
by Celauran
aneuryzma wrote:I don't need the number of rows in my result set (mysql_num_rows() returns this). I need the row number in the original table (i.e. second row of the table).
That's going to depend on how the table is sorted. If you want the ID of the primary key, then just SELECT it.
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 12:48 pm
by aneuryzma
New rows are going to be added to the table, so I would like to sort it chronologically and not by a specific field. Should I add a new field with a counter or it is not necessary ?
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 1:06 pm
by Celauran
aneuryzma wrote:Should I add a new field with a counter or it is not necessary ?
Wouldn't that just be an auto-incremented primary key?
Re: Getting the Mysql row number ?
Posted: Thu Sep 01, 2011 4:04 pm
by social_experiment
aneuryzma wrote:How can I get the number of a row fetched from a table (for example, given a specific value) ?
Ok, your question was a bit misleading

Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 1:20 am
by aneuryzma
What about the answer ?

Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 1:21 am
by aneuryzma
Do I need to add an auto-incremented primary key? or I don't need this field ?
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:24 am
by ok
aneuryzma wrote:New rows are going to be added to the table, so I would like to sort it chronologically and not by a specific field. Should I add a new field with a counter or it is not necessary ?
The simplest solution is to add a timestamp column to the table. On each "important" update to a row, you then update the timestamp in the timestamp column.
If you want to order chronologically, you just order by the timestamp column.
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:28 am
by aneuryzma
The update of a row shouldn't actually changing the order. I need something very simple, having an ID for each row. If a new field ID (1,2,3...) is needed I will add it, but if this is not necessary it would be great.
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:38 am
by ok
Well, the timestamp column is good for ordering the results chronologically. The events on which you update the timestamp of a row are completely decided by you.
If you use timestamp, you can easily know the next "count", as opposed to a regular counter where you need to query the database in order to know the last number...
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:44 am
by aneuryzma
I see thanks. so I do need an additional field. I can't just get the rows as I see them ordered in the table (without any sorting) they have a chronological sorting, for example, in phpmyadmin).
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:46 am
by ok
I think that in phpMyAdmin the rows are ordered by the primary key of the table. Most of the time this type of sorting is chronological, however, it might not be.
Re: Getting the Mysql row number ?
Posted: Fri Sep 02, 2011 2:50 am
by aneuryzma
1. I've never specified the primary key of the table. Is it specified automatically ?
2. I don't see any alphabetical sorting to any of my fields. The sorting is just chronological (at least as they are displayed), if I fetch results I'm probably not going to have the same sort guaranteed.