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
Display second record in table, third record in table, etc.
Moderator: General Moderators
Re: Display second record in table, third record in table, etc.
Use ORDER BY together with LIMIT
There are 10 types of people in this world, those who understand binary and those who don't
Re: Display second record in table, third record in table, etc.
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.
You can use LIMIT to do that.
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.
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.
Code: Select all
...ORDER BY date DESC LIMIT 5, 1 # starting at the sixth record, select one rowIf 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, 3Re: Display second record in table, third record in table, etc.
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.