Displaying events on a calendar

[foodpk]

Hi!
I'm trying to make a scheduler with PHP and MySQL. Basically it should display a calendar and then display names of events in each day. So I've already made it display a very neat calendar, it displays the calendar for any number of months, highlights the current day etc.
Now I want to make it so that if I have an event on say June 20th it displays the name of that event under June 20th. Here's the event table.


+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| EventID | int(11) | NO | PRI | NULL | auto_increment |
| CategoryID | int(11) | NO | MUL | NULL | |
| Name | varchar(50) | NO | | NULL | |
| Start | int(11) | NO | | NULL | |
| End | int(11) | YES | | NULL | |
| Info | text | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+

Also here's the php code.


$conn = mysql_connect('localhost','root','password');
mysql_select_db('calendar');
if (!isset($_GET['month'])) {
$year = date("Y", time());
$month = date("n", time());
$mdepth = 1;
} else {
$year = $_GET['year'];
$month = $_GET['month'];
$mdepth = $_GET['mdepth'];
}
$time = mktime(0,0,16,$month,1,$year);
echo "<ul class=\"calendar\">";
for($y=0;$y<=$mdepth;$y++) {
echo "<li style=\"background-image:url(month_bg.gif)\">". date("F Y", mktime(0,0,16,$month+$y,1,$year)) . "</li>";
for($x=1;$x<=date("t", mktime(0,0,16,$month+$y,1,$year));$x++) {
echo "<li";
if (date("j m", mktime(0,0,16,$month+$y,$x,$year)) == date("j m",time())) {
echo " style=\"background-image:url(date_bf2.gif)\"";
}
echo ">";
echo date("l jS", mktime(0,0,16,$month+$y,$x,$year))."</li>";
}
}
echo "</ul>";
$query = 'SELECT eventid, name, start, end FROM event';
$result = mysql_query($query);
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $line['name'] . " " . date("l");
}

I'd know how to implement it with code but I'm stumped logistically here. One option would be that for each displayed date to have a query which gets the event that is on that date. But that would leave the scheduler making 60+ queries per page and I don't like that. I could have one query and then for instance if the date is June 20th have it stored in an array under [6][20] but that would enable only one event per day.
So, any suggestions about how I should go about solving this logistically?

[skOOb]

Maybe have 1 query like you said but store it in a 2-dimensional array, like "events[n][name]" and "events[n][start]" ... one for every entry in your events table. Then when you are outputting that day, you could have a loop running through your events array looking for events that are between the starting and ending dates. You will have all the information to display in the array, so you won't have to query the database to get the information. May be kinda slow, but probably faster than all those extra queries, and it will let you have multiple events per day.

hth, or gives you another idea.

edit:// Obviously only query the events in the date range you are displaying...just thought I would mention this.

[foodpk]

Hmmm, yeah.
Reading your post gave me an idea to put them into a 2d array, query the db to give them to me in the reverse order and then use the 2d array as a stack. Check if the date matches the last array entry and if it does, pop the last value from the stack and display all the events for that day.
That means that I have only one query and no searching through arrays. Works perfect!
thanks!