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
9

Instead of querying the database inside the loop for each day (which is inefficient), fetch all events for the month in a single query.

// 1. Define year and month
$year = 2025;
$month = 5; // May

// 2. Query all events for this month
$startDate = "$year-$month-01";
$endDate = date("Y-m-t", strtotime($startDate)); // last day of month

// Assuming you have a MySQL connection $conn
$sql = "SELECT event_date, event_name FROM events WHERE event_date BETWEEN '$startDate' AND '$endDate'";
$result = $conn->query($sql);

// 3. Organize events by day
$eventsByDay = [];
while ($row = $result->fetch_assoc()) {
    $day = (int)date('j', strtotime($row['event_date']));
    $eventsByDay[$day][] = $row['event_name'];
}

// 4. Get number of days in month
$daysInMonth = date('t', strtotime($startDate));

// 5. Generate calendar days
echo "<table><tr>";
for ($day = 1; $day <= $daysInMonth; $day++) {
    echo "<td>";
    echo $day;
    
    // Show events for the day
    if (isset($eventsByDay[$day])) {
        foreach ($eventsByDay[$day] as $event) {
            echo "<br><small>$event</small>";
        }
    }
    echo "</td>";
    
    // Optionally start new rows for weeks (every 7 days)
    if ($day % 7 == 0) {
        echo "</tr><tr>";
    }
}
echo "</tr></table>";
  • Do not query the database inside your for loop.
  • Fetch data once, then display it.
  • Use a PHP array to link days with events.
  • This method scales well and keeps the code clean.
10

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.