Sorting through records in a database by date..

chronomantic

Sorry, it was hard to come up with an appropriate title for the question. But I'm wondering if this will be as complex to solve as I'm hoping it won't be.

On my website I post a lot of articles and I have the option for 'next article' and 'previous article' on every full article's page. Previous article is simply the article that was posted after that one, and next article is the article whose date was posted just before the current one. So my query simply looks like this:

SELECT fields, needed FROM articles WHERE id < $current_id LIMIT 1

But, if I have, say, four articles with a posting date of April 4th at 12:00 AM, my next and previous links can become a loop.... What's the best way to solve this? Just make sure no articles have the exact same posting date?

Thanks for any insight.. It's appreciated!

dagon

your limiting your query to return one result and its by id, not date, so you are always going to get 1 result. Did you want the 4?

Weedpacket

Order by date, and use, say, the ID, to break any ties: "ORDER BY the_date, id".

Same idea if you only want one: for the previous post, either the date and time are earlier, or the date and time are the same and the ID is smaller; if there are several such, pick the one with the largest ID.

For the next post, you want the first one that's later...

shamly

how about something like this ...

at the top of the page define a variable and set its value to 0.

e.g. 
if(isset($_GET['rdc']))
{
   $rec_display_count = $_GET['rdc'];

}   else
{
    $rec_display_count = 0;
}

write the sql query like this

SELECT fields, needed FROM articles WHERE id < $current_id LIMIT $rec_display_count, 1

so when the page loads for the first time you will get the top most result.
On the next article link pass the incremented value of $rec_display_count (say the link is index.php?rdc=1).

once the next page loads the value of $rec_display_count will be 1 and the sql query will fetch the second result from the result set ..

This is a basic coding, but needs much more fine tuning. But i hope you get the basic idea.