I have a simple query:
SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year>=YEAR(CURDATE()) AND month>=MONTH(CURDATE()) AND day>=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC
Results:
+----+------+-------+-----+--------+ | id | year | month | day | cat_id | +----+------+-------+-----+--------+ | 25 | 2003 | 12 | 7 | 5 | | 44 | 2004 | 2 | 15 | 2 | | 53 | 2004 | 3 | 28 | 5 | | 45 | 2004 | 6 | 6 | 2 | +----+------+-------+-----+--------+
Can anyone point out where I've screwed up the query?? I don't think it should be giving me the first entry. It is a trivial problem, and I can filter the results further with php, but I'd like to do it right, and am trying to learn SQL.
John,
It looks like you're storing the date as 3 separate columns, Year, Month, Day. This is definitely NOT the way to do it. You need to create a Date column and call it something like, Cal_Date (or make it a DateTime if you want the time of the event). Now you can do proper comparisons between 2 dates. Your existing 3 column comparison will break down quickly if the stored day is less than the current day but the stored month and year is greater than the current date.
Example: Year=2004, Month=3, Day=1
Your query will fail because Day 1 < 4 (if today is Feb 4th).
You basically want all events that are scheduled for today or in the future.
If you get rid of Year, Month, Day columns and replace them with cal_date, you get something much simpler (and your head won't hurt so much<g.):
SELECT id, year(cal_date) as Year, month(cal_date) as Month, day(cal_date) as Day, cat_id FROM events
LEFT JOIN calendar_cat ON
events.cat=calendar_cat.cat_id
WHERE cal_date >= CURDATE()
AND cat_id='2' OR cat_id='5'
AND approved='1'
ORDER BY cal_date ASC
So you use a single column to store the date, in this case cal_date, and use functions like Year(), Month(), Day() to extract the values for your display. There are a pile of date functions that are at your disposal. Your Where clause and sort always refers to the underlying cal_date Date column. For a list of date functions, see http://www.mysql.com/doc/en/Date_and_time_functions.html.
'hope this helps. :)
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
