I have a working query that pulls all appointments in the table for the day. I have a working calendar that builds the month and highlights the current day. I want to add the query to the calendar and just count the number of appointments for each day and show the number under the day of the month. The calendar is from a tutorial I found online. Any help is much appreciated.
Query I currently use to pull appointments for the current day.
$sql = <<<SQL
SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId, appointments.complete, client.clientId, dog.dogId
FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId
LEFT JOIN dog ON appointments.dogId = dog.dogId
WHERE appointments.start > CURDATE()
ORDER BY appointments.start ASC
SQL;
if(!$result = $db->query($sql)){
die('There was an error while running the query [' . $db->error . ']');
}
$num_rows = $result->num_rows;
Calendar I am trying to use.
<?php
date_default_timezone_set('America/Denver');
function build_calendar($month,$year,$dateArray) {
$today_date = date("d");
$today_date = ltrim($today_date, '0');
// Create array containing abbreviations of days of week.
$daysOfWeek = array('Sun','Mon','Tue','Wed','Thr','Fri','Sat');
// What is the first day of the month in question?
$firstDayOfMonth = mktime(0,0,0,$month,1,$year);
// How many days does this month contain?
$numberDays = date('t',$firstDayOfMonth);
// Retrieve some information about the first day of the
// month in question.
$dateComponents = getdate($firstDayOfMonth);
// What is the name of the month in question?
$monthName = $dateComponents['month'];
// What is the index value (0-6) of the first day of the
// month in question.
$dayOfWeek = $dateComponents['wday'];
// Create the table tag opener and day headers
$calendar = "<table border='1px'>";
$calendar .= "<caption>$monthName $year</caption>";
$calendar .= "<tr>";
// Create the calendar headers
foreach($daysOfWeek as $day) {
$calendar .= "<th class='header' width='50px' height='50px'>$day</th>";
}
// Create the rest of the calendar
// Initiate the day counter, starting with the 1st.
$currentDay = 1;
$calendar .= "</tr><tr>";
// The variable $dayOfWeek is used to
// ensure that the calendar
// display consists of exactly 7 columns.
if ($dayOfWeek > 0) {
$calendar .= "<td colspan='$dayOfWeek' width='50px' height='50px'> </td>";
}
$month = str_pad($month, 2, "0", STR_PAD_LEFT);
while ($currentDay <= $numberDays) {
// Seventh column (Saturday) reached. Start a new row.
if ($dayOfWeek == 7) {
$dayOfWeek = 0;
$calendar .= "</tr><tr>";
}
$currentDayRel = str_pad($currentDay, 2, "0", STR_PAD_LEFT);
$date = "$year-$month-$currentDayRel";
if($currentDayRel == $today_date ){ $calendar .= "<td class='day' id='today_date ' rel='$date' width='50px' height='50px' alighn='center' valign='top'align='left'><a href='appointmentdate.php?date=$date'><b>$currentDay</b></a></td>"; }
else { $calendar .= "<td class='day' rel='$date' width='50px' height='50px' valign='top' align='left'><a href='appointmentdate.php?date=$date'>$currentDay</a></td>"; }
// Increment counters
$currentDay++;
$dayOfWeek++;
}
// Complete the row of the last week in month, if necessary
if ($dayOfWeek != 7) {
$remainingDays = 7 - $dayOfWeek;
$calendar .= "<td colspan='$remainingDays'> </td>";
}
$calendar .= "</tr>";
$calendar .= "</table>";
return $calendar;
}
?>
<?php
error_reporting(0);
$dateComponents = getdate();
$month = $dateComponents['mon'];
$year = $dateComponents['year'];
echo build_calendar($month,$year,$dateArray);
?>
I have error reporting off because I get an undefined variable $dateArray when I echo build_calendar on the last line of the calendar code. Not sure why I am getting a error.