Hi, i have a sql problem; I have some records ordered by date, and using PHP i can view the contents of an item in detail in my browser. However, i need a previous and next link to be able to jump quickly to other items.

However, since some of my items have the same date i have been having problems with selecting these previous and next items.

Here is a sample Database output :

SQL code to generate this :

Holiday Dinner Oct 29th
Just Married Oct 26th
The geography Oct 26th
Top 10 slogans Oct 26th
Pregnant Oct 25th
Temptation Oct 25th

Next Item SQL =

Previous Item SQL =

the $.. are variables, collected from the item the user is looking at.

When i look at an item in detail, here are the previous and next item suggestions:

Previous = Newer item
Next = Older item

holiday dinner:
NEXT -> just married
PREVIOUS -> /

just married
NEXT -> The geography
PREVIOUS -> top 10 slogans INCORRECT

The geography
PREVIOUS -> top 10 slogans INCORRECT
NEXT -> just married INCORRECT

top 10 slogans
PREVIOUS -> the geography
NEXT -> just married INCORRECT

pregnant
PREVIOUS -> tempation INCORRECT
NEXT -> temptation

Does anyone know how I can fix this? I'm using date at the moment, don't think datetime would make a difference, because i still want them grouped by day/month and then title.

Thank you very much

I would say to turn the date column into datetime. This way, you could have an order specified within the days with more than one title, but still group by month / day.
It might take some work to get the datetime column ordered right. You could also just add an order column and number the titles by the order you know.
Does this help?

But wouldn't i then be ordering on time if more than one item has the same date (day)? I don't quite understand, could you elaborate a bit :s

Btw, don't think an order column would be good, with every new, changed or deleted item I'd have to re-order that.

Thanks,
Wesley

By changing the date to datetime, you can give an order to the titles that are on the same day, but it wouldn't cause problems when you insert or delete. You don't need to consider the time part of the column once the order is set. If you had two titles on the same day and you knew the ordering, then you could give them values like this:

title 1 9/23/2003 01:01:01
title 2 9/23/2003 02:01:01

The time values do not need to mean anything. It would take care of the misordering and you wouldn't have to change the SQL statements that you have.