mysql - select distinct date ....

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):
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
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.

I'm not 100% clear exactly what you're trying to achieve but do you really need to SELECT DISTINCT rather than plain old SELECT?

It sounds like you're wanting to get all of the entries for a particular month but grouped together according to their associated day. You can either bring them all back, and use PHP to 'group' by the day or you can only select one from each day (DISTINCT event_date).

i need it to go:

date with event
event 1
event 2
event 3

date wth event
event 1
event 2

etc.

i just said **** it and changed it the table from 1 datetime field to 2 fields, date and time.

it was less work than i thought. in my classes the only thing i had to change were the insert/update queries so they wrote to 2 fields instead of combining the two $_POST variables into 1 datetime.

edit: the select distinct was part 1 of a series of methods:

* get all distinct dates for this month
* get all events based on sorting parameters
* for each date, loop through that day's events

if there's a more resourceful way to do this, i'm all ears but this was the most straight forward i could come up with.