ok, so i need someone with a good background in mysql and some time on their hands.
the situation:
i have a php calendar script that talks to my database and displays all entries in my events table. it's not looping all the events per date properly. i use a datetime field, which turns out to be the problem. the first time i used this script, i had separate date and time fields because they asked for time AFTER i had built it.
the query i used (the first time with separate date and time fields):
Code:
"SELECT DISTINCT event_date FROM tbl_events WHERE MONTH(event_date) = '$month' AND YEAR(event_date) = '$year' ORDER BY event_date ASC"
this worked because 2007-09-24 == 2007-09-24
the problem? datetime
it's getting all the distinct datetimes within the specified month/year, so it's not looping my entries properly: 2007-09-24 12:00:00 != 2007-09-24 1:00:00
i tell it to print all where the date = $day, but since i have 2 or more sets for each date, 2+ get lost in limbo because it already printed 1 and moved onto the next date.
i have two options:
1) change the table to two separate fields and re-check all my methods
2) change the query
i tried
Code:
SELECT DISTINCT DATE(event_date) FROM tbl_events WHERE YEAR(event_date) = '$year' ORDER BY event_date ASC
but no luck. i'm still working off test entries in the database, so it's not like i have to go back and edit everything, i can just wipe and start clean, but if i can figure the query out, it's a bonus so i want to try that first.