Page 1 of 1

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

Posted: Thu Jan 08, 2009 2:14 pm
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

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

Posted: Thu Jan 08, 2009 3:33 pm
by VladSun
Use ORDER BY together with LIMIT

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

Posted: Thu Jan 08, 2009 6:59 pm
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.

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

Posted: Thu Jan 08, 2009 7:50 pm
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.

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

Posted: Thu Jan 08, 2009 7:59 pm
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.