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?
