I have a website which allows Gyms and Customers to join. The Gym owner can add their classes and update their schedule. There’s 3 important tables in this scenario (gyms, gym_classes, gym_schedules).
Gym owners can have multiple gyms, but each gym has it’s own schedule.
The schedule will be the same weekly, so my gym_schedule table has the following columns
id | gym_id | class_id | day_of_week | start_time
As this repeats weekly, I insert the day of the week as an integer, 1 to 7 (Mon to Sun).
My Form is as so
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . '?gym_id=' . $gym_id); ?>" method="post">
<?php for ($day = 1; $day <= 7; $day++): ?>
<h3><?php echo $dayNames[$day]; ?></h3>
<label for="class_id_day<?php echo $day; ?>">Select Classes and Start Times:</label>
<br>
<?php foreach ($classes as $class): ?>
<?php
$class_id_key = 'class_id_day' . $day;
$start_time_key = 'start_time_day' . $day;
// Check if the class has a schedule for the current day
$isChecked = isset($schedule_by_day[$day][$class['id']]);
$startTimeValue = $isChecked ? $schedule_by_day[$day][$class['id']] : '';
?>
<input type="checkbox" name="schedule[<?php echo $day; ?>][class_id][]" value="<?php echo $class['id']; ?>" <?php if ($isChecked) echo 'checked'; ?>>
<?php echo $class['class_name']; ?>:
<!-- With this -->
<select name="schedule[<?php echo $day; ?>][time][]">
<?php
$selectedTime = $startTimeValue ? date('H:i', strtotime($startTimeValue)) : '';
for ($hour = 0; $hour < 24; $hour++) {
for ($minute = 0; $minute < 60; $minute += 15) {
$time = sprintf('%02d:%02d', $hour, $minute);
echo '<option value="' . $time . '" ' . ($selectedTime === $time ? 'selected' : '') . '>' . $time . '</option>';
}
}
?>
</select>
<br>
<?php endforeach; ?>
<br>
<?php endfor; ?>
<input type="submit" value="Save Schedule">
</form>
This renders on my page like…
Monday
Select Classes and Start Times:
Class A: **SELECT FIELD**
Class B: **SELECT FIELD**
Class C: **SELECT FIELD**
My PHP insert is at the top of the page…
// Map day numbers to day names
$dayNames = array(
1 => 'Monday',
2 => 'Tuesday',
3 => 'Wednesday',
4 => 'Thursday',
5 => 'Friday',
6 => 'Saturday',
7 => 'Sunday'
);
// Process the form data to set the gym schedule
if ($_SERVER["REQUEST_METHOD"] === "POST") {
try {
// Start a transaction
$pdo->beginTransaction();
// Delete existing schedule data for the gym
$stmt = $pdo->prepare("DELETE FROM gym_schedule WHERE gym_id = :gym_id");
$stmt->bindParam(':gym_id', $gym_id);
$stmt->execute();
// Loop through the days of the week (1 to 7, where 1 is Monday and 7 is Sunday)
for ($day = 1; $day <= 7; $day++) {
// Get the selected classes and start times for each day
if (isset($_POST["schedule"][$day]['class_id']) && isset($_POST["schedule"][$day]['time'])) {
$class_ids = $_POST["schedule"][$day]['class_id'];
$start_times = $_POST["schedule"][$day]['time']; // Use 'time' instead of 'start_time'
foreach ($class_ids as $index => $class_id) {
// Get the selected time for this class on this day
$start_time = $start_times[$index];
// Check if the start time for this class on this day is empty
// If empty, skip inserting this record
if (empty($start_time)) {
continue;
}
$stmt = $pdo->prepare("INSERT INTO gym_schedule (gym_id, class_id, day_of_week, start_time)
VALUES (:gym_id, :class_id, :day_of_week, :start_time)");
$stmt->bindParam(':gym_id', $gym_id);
$stmt->bindParam(':class_id', $class_id);
$stmt->bindValue(':day_of_week', $day);
$stmt->bindParam(':start_time', $start_time);
$stmt->execute();
}
}
}
// Commit the transaction
$pdo->commit();
} catch (PDOException $e) {
// Rollback the transaction in case of an error
$pdo->rollBack();
die("Error: " . $e->getMessage());
}
// Redirect after processing form data
header("Location: my-gyms.php");
exit();
}
With this, I’m getting some really mixed results. Some days the class and its time are inserted correctly, others the class is inserted with the time set to 00:00:00 and others no record is inserted at all.
I’ve dumped the results of the array and all looks okay so I presume its to do with my insert statement? I’ve also tried using a time input which didn’t work.
I’ve become unstuck, I tried ChatGPT to no avail and I can’t think of anything else now to get this working.