I have a script on my website which produces a WEEK-AT-A-GLANCE diary of my appointments. Currently, I have coded the SQL such that it selects only those dates in the current week (MON-SUN).
select LESSON_ID, PUPIL_ID, DATE, TIME, DURATION,
TIME_FORMAT(TIME,'%H') 'HOUR',
TIME_FORMAT(TIME,'%i') 'MINS',
TIME_FORMAT(DURATION,'%H') 'DURHOURS',
TIME_FORMAT(DURATION,'%i') 'DURMINS',
DAYOFWEEK(DATE) 'DAY'
from LESSON where WEEK(DATE,1) = WEEK(now(),1)
order by DATE asc, TIME asc
I am using MySQL.
What I want to do is have a link at the bottom of the screen which says 'NEXT WEEK', and of course that will show me next weeks diary.
I propose to use a querystring variable, e.g.
<A HREF='view_dairy.php&week=1'>NEXT WEEK</A>
Then I can use some PHP to read that and modify the SQL statement accordingly. So if I click 'NEXT WEEK' the SQL will somehow add 1 week to the current week - WEEK(now(),1).
I've tried using DATE_ADD but I never seemed to get any valid SQL.
Does anyone have any suggestions ?
Thanks in advance
