Re: next/previous on a date select query

[Rick James]

Bravo! You are already on the road to doing pagination the optimal way!

There is no need for
DATE(date) >= DATE(NOW())
Instead, simply say
date >= NOW()
or
date >= CURRENT_DATE()

Can there be two events in the same day?

For the moment, let's say there cannot be.

Your Next and Prev buttons need to have some extra information. For the moment, let's say the extra info is the ID value ($left_off) of the last item on the page (on the Next button).

Using a self-join:

SELECT  a.ID, a.djs, a.photo, a.comments,
        UNIX_TIMESTAMP(a.date) AS unixdate
    FROM  test a, test b
    WHERE  a.date > b.date
      AND  b.ID = $left_off
    ORDER BY  a.date ASC
    LIMIT  3

or, using a subquery:

SELECT  a.ID, a.djs, a.photo, a.comments,
        UNIX_TIMESTAMP(a.date) AS unixdate
    FROM  test a
    WHERE  a.date > ( SELECT date FROM test WHERE ID = $left_off )
    ORDER BY  a.date ASC
    LIMIT  3

(Prev page would be done in an analogous way.)

If there can be duplicate `date` values, then it gets messier. The original query needs to have

    ORDER BY date ASC, ID ASC

in order to disambiguate the duplicate dates. But my suggestions for Next page need more than just that...

SELECT  a.ID, a.djs, a.photo, a.comments,
        UNIX_TIMESTAMP(a.date) AS unixdate
    FROM  test a, test b
    WHERE  a.date >= b.date  AND  ( a.date > b.date OR a.ID > b.ID )
      AND  b.ID = $left_off
    ORDER BY  a.date ASC, ID ASC
    LIMIT  3

(The subquery method won't work.)

Now, to make your web site even nicer, you should gray out (or remove) the Next button if there are no more items. The efficient way to do that is
* Fetch an extra item (LIMIT 4 instead of LIMIT 3)
* Don't display the extra item
* If you did not get an extra item, then (via PHP code) gray out the Next button.

BTW, you should have
INDEX(date, ID)



Edited 1 time(s). Last edit at 07/05/2010 03:42PM by Rick James.