Populate a calendar with database data

PHP
1

Ok so I’m using a for loop to generate a calendar, but I also have a mysql query to populate each of the days with different events. How would I go about incorporate the sql query into the generation of the month ? I feel like I can’t wrap my head around it lol

thanks for reading!

2

Are you generating only 1 month on the page?

General rule of thumb will be to never put a query inside a loop…

1 Like
3

The simplest, fastest method is to query all at once for the data that corresponds to the date range of the calendar you are displaying, index/pivot the data using the date as the main array index when you fetch it, then as you are looping to produce the output, use the current date being displayed to test (isset()) if there is an entry in the fetched data to be output on that date.

4

WIth some help from chat GPT I got this, but I can’t get the array to populate the days


$userID = 7;
$month =  date('n');
$year =  date('Y');


// 2. Fetch events from database
$stmt = $pdo->query('SELECT * FROM checkins WHERE userID = '.$userID.' AND MONTH(timestamp) = '.$month.' AND YEAR(timestamp) = '.$year.' ORDER BY DAY(timestamp), HOUR(timestamp) ASC');
$events = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    
    //remove time from datestring
    $createDate = new DateTime($row['timestamp']);
    $strip = $createDate->format('Y-m-d');
    
    //populate the array
    $events[$strip][] = $row['tankID'];
}

// 3. Define the month and year for the calendar
$daysInMonth = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$firstDayOfMonth = date('w', strtotime("$year-$month-01")); // Sunday = 0

// 4. Start generating calendar HTML
echo "<table border='1'>";
echo "<tr><th>Sun</th><th>Mon</th><th>Tue</th><th>Wed</th><th>Thu</th><th>Fri</th><th>Sat</th></tr><tr>";

// 5. Add empty cells for the first week
for ($i = 0; $i < $firstDayOfMonth; $i++) {
    echo "<td></td>";
}

// 6. Loop through all days of the month
for ($day = 1; $day <= $daysInMonth; $day++) {
    $currentDate = $year.'-'.$month.'-'.str_pad($day, 2, "0", STR_PAD_LEFT);
    echo "<td><strong>$day</strong>";

    // Check if this day has any events
    if (isset($events[$currentDate])) {
        foreach ($events[$currentDate] as $event) {
            echo $event;
        }
    }

    echo "</td>";

    // New row each week
    if ((($day + $firstDayOfMonth) % 7) == 0) {
        echo "</tr><tr>";
    }
}

// 7. Fill the rest of the last week with empty cells
$remainingCells = (7 - (($daysInMonth + $firstDayOfMonth) % 7)) % 7;
for ($i = 0; $i < $remainingCells; $i++) {
    echo "<td></td>";
}

echo "</tr></table>";
print_r($events);
5

yes, I have a form that lets the user choose month and year, but default is current

6

Looks like my $month needed to be changed from n to m

7

Well, you needed to be consistent at least.

They either both had to be “n” or “m”. n has no zero-pad, m does.

1 Like