Display second record in table, third record in table, etc.

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

Post Reply
twebman
Forum Newbie
Posts: 1
Joined: Thu Jan 08, 2009 2:10 pm

Display second record in table, third record in table, etc.

Post by twebman »

Using mySQL and PHP I want to select the records from my table and then display the second most recent record (based on a field in that table, like postID or date, whetaver) on the web page.

For example, suppose I have a table called blogPosts and the fields are postID, title, blurb, and post ... In that table I have 50 records, with the postID being incremental from 1-50. 50 is the most recent post. How can I select the record with the postID of 49 (or whatever record is the second in descending order) ? Furthermore, can I select the 3rd highest and 4th highest, etc.?

Thanks
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Display second record in table, third record in table, etc.

Post by VladSun »

Use ORDER BY together with LIMIT
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Display second record in table, third record in table, etc.

Post by califdon »

I think he doesn't want the first n rows, just the nth row. You probably have to do what Vladsun suggested, then count down through the rows with PHP.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Display second record in table, third record in table, etc.

Post by requinix »

You can use LIMIT to do that.

Code: Select all

...ORDER BY date DESC LIMIT 5, 1 # starting at the sixth record, select one row
Make sure you sort by date, not postID: the date actually has a meaning.

If you want the second, and third, and fourth at once, don't run three separate queries.

Code: Select all

...ORDER BY date DESC LIMIT 1, 3
The first thing you get from that query will be the #2 result, the next will be the #3, and the last will be #4.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Display second record in table, third record in table, etc.

Post by califdon »

Sheesh! You're right, of course! Also, I agree on using meaningful data, not an arbitrary ID field, at least in most cases. ID's are primarily to establish unique identifiers, not convey information about the data. While it might produce the same results in a specific case, it's a bad habit to form.
Post Reply