"me you" <[EMAIL PROTECTED]> wrote on 07/20/2005 03:05:30 PM:
> Hello,
>
> Having some problems trying to get the correct data from MySQL. I need
to
> grab a bunch of information based on a date the user selects (no problem
> there). However, this is an events type situation, where I want to give
> people an easy option to move forward or backwards through the data once
the
> initial results have been displayed.
>
> Special note (the tricky part) Because this is an events type of
situation,
> not all dates will actually have data available.
>
> SAMPLE DATA:
>
> id date info
> -------------------------------------------------------
> 1 2005-01-12 big event happened today
> 2 2005-01-15 some event happened today
> 3 2005-03-01 a birthday happened today
> 4 2005-03-10 another birthday here
>
> Eg.,
>
>
> User selects (March 1, 2005)
>
> We display the information from March 1, 2005. On that page, I want to
be
> able to display a <NEXT EVENT> and a <PREVIOUS EVENT> option via simply
html
> links.
>
> If user clicks NEXT EVENT it should know to get the data from 2005-03-10
and
> if the user clicks PREVIOUS EVENT, then they'll get the information from
the
> 2005-01-15 event.
>
> Any help would be appreciated.
>
> Thanks
I would do it in 3 queries:
set @targetdate='2005-03-01';
/* q1 - determine "prev" date */
SELECT @prevDate := MAX(datefield) from tablename where user_id=0000 and
datefield < @targetdate;
/* q2 - determine "next" date */
SELECT @nextDate := MIN(datefield) from tablename where user_id=0000 and
datefield > @targetdate;
/* q3 - get target date */
SELECT @prevDate as prevdate, @nextDate as nextdate, datefield, textfield
from tablename where datefield = @targetdate;
Of course, if you had provided ANY actual information about your tables,
my queries could have used your actual field and table names.... >8-{
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
