Getting the Mysql row number ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Getting the Mysql row number ?

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Getting the Mysql row number ?

Post by social_experiment »

mysql_num_rows() or using COUNT()
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post 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).
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting the Mysql row number ?

Post 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.
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post 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 ?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Getting the Mysql row number ?

Post 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?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Getting the Mysql row number ?

Post 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 ;)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post by aneuryzma »

What about the answer ? :)
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post by aneuryzma »

Do I need to add an auto-incremented primary key? or I don't need this field ?
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Re: Getting the Mysql row number ?

Post 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.
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post 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.
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Re: Getting the Mysql row number ?

Post 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...
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post 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).
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Re: Getting the Mysql row number ?

Post 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.
aneuryzma
Forum Contributor
Posts: 106
Joined: Sat May 17, 2008 7:03 am

Re: Getting the Mysql row number ?

Post 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.
Post Reply