The Next Date in DB

slindstr

Hey everyone I could really use some help with this one - I've tried a bunch of things and nothing has worked yet.

I have a database of articles, each with a date. When one article is displayed I need two links, one to go to the next article (a newer date) and one to go to a previous article (an older date). How do I do this?

I tried this query and it didn't work out so well:

SELECT ID FROM entries WHERE userID='46' AND DATE_SUB('$postDateFull',INTERVAL 100 YEAR) < date ORDER BY date DESC LIMIT 1

I wish I could just use something like:

SELECT ID FROM entries WHERE userID='46' AND date < $specifiedDate ORDER BY date DESC LIMIT 1

I'd appreciate any help I can get.

Thanks
Steve

Sxooter

If you've got subqueries you could do something like this:

select from test where ts < (select ts from test where id=3 ) order by ts desc limit 1;
select from test where ts > (select ts from test where id=3 ) order by ts limit 1;

That should get one from each end. With a union around them you could get both, but I know that postgresql freaks if you try to use a limit in the middle of a union. If your database does too, then you can get around it with a subselect wrapper. Like this:

select * from (
    select * from test where ts > (
        select ts from test where id=3 ) order by ts limit 1
    ) as a 
union 
select * from (
    select * from test where ts < (
        select ts from test where id=3) order by ts desc limit 1
    ) as b;

If you don't have subqueries, then just do the queries one at a time and use the dates in each query. I.e. chop these queries up into pieces...

slindstr

Thanks I'll give it a shot!

Roger_Ramjet

You can actually use order by and limit in union queries in mysql, Sxooter, so a straight union query would work.

(select * from articles where article_date < $subdate order by article_date limit 1)
union
(select * from articles where article_date = $subdate)
union
(select * from articles where article_date > $subdate order by article_date desc limit 1)

That will work and return them in the same order as the select queries.

NB note the brackets around each select query. They are needed for this to work.

edit: help if I got the order by right 😃

Sxooter

Yeah, I think the same trick will work in PostgreSQL too.