When you first look at a booking system, it seems like a simple CRUD app: you create a reservation, you read it, you update it, and you delete it. But the real challenge isn't the database interaction; it's the logic of availability. If you don't handle time overlaps correctly, you'll end up with two people showing up for the same massage appointment at 2:00 PM, and that's a quick way to get fired.

For this lesson, let's build a small booking engine for a personal trainer. We only need to track one thing: is the trainer free between $start and $end?

Setting up the timeline

I prefer using DATETIME fields in MySQL for this because they handle the date and time in one go, which simplifies our queries. I've already set up a table called appointments with start_time and end_time columns. Here is how I'm thinking about the logic: a slot is taken if any part of the requested time range intersects with an existing record.

public function isSlotAvailable($start, $end) {
    $stmt = $this->db->prepare("SELECT COUNT(*) FROM appointments 
                                WHERE start_time < ? AND end_time > ?");
    $stmt->execute([$end, $start]);
    return $stmt->fetchColumn() == 0;
}

The "Aha!" moment and my classic mistake

Now, if you're looking at the code above, you might think it's too simple. In fact, when I first wrote this logic years ago, I did something much more complex. I tried to write four different OR conditions: checking if the start was inside a booking, if the end was inside a booking, if the booking was inside the request, and so on. It was a nightmare to debug.

But then I made a different mistake. I initially wrote the query like this: WHERE start_time = ? AND end_time = ?. I was thinking in "slots" (like 2:00 to 3:00). But users don't always book in perfect blocks. What if someone books from 2:15 to 3:15? My exact-match query would have let that slide right through, resulting in a double-booking.

The trick I use now—and the one you should remember—is the overlap principle: Two time ranges overlap if the start of one is before the end of the other, AND the end of one is after the start of the other. It covers every single scenario: partial overlaps, total encompasses, and exact matches.

Wrapping it in a Booking Manager

Let's put this into a concrete class. I'm going to include a method to actually perform the booking, but only after we've verified the slot is open. I'm using a transaction here because in a high-traffic system, two people might check availability at the exact same millisecond. Without a transaction or a lock, they both see "available" and both get booked.

class BookingManager {
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    public function bookSession($clientId, $start, $end) {
        try {
            $this->pdo->beginTransaction();

            // We use 'FOR UPDATE' to lock these rows until the transaction ends
            $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM appointments 
                                         WHERE start_time < ? AND end_time > ? 
                                         FOR UPDATE");
            $stmt->execute([$end, $start]);

            if ($stmt->fetchColumn() > 0) {
                throw new Exception("This time slot is already taken.");
            }

            $insert = $this->pdo->prepare("INSERT INTO appointments (client_id, start_time, end_time) 
                                           VALUES (?, ?, ?)");
            $insert->execute([$clientId, $start, $end]);

            $this->pdo->commit();
            return true;
        } catch (Exception $e) {
            $this->pdo->rollBack();
            return $e->getMessage();
        }
    }
}

Notice how I passed $end as the first parameter and $start as the second in the SELECT query. It feels counter-intuitive at first, but it's the mathematically cleanest way to detect any overlap. If the existing appointment starts before your requested end, and ends after your requested start, there is no world where those two events don't collide.

📋 Practical Task

Implement an Overlap Checker for a Yoga Studio

You are building a booking system for a Yoga Studio. You have an array of existing bookings, where each booking is an associative array with 'start' and 'end' keys (represented as Unix timestamps).

Write a PHP function called validateBooking that takes the $existingBookings array and a $requestedBooking array. The function should return true if the requested slot is free, and false if it overlaps with any existing appointment.

Example Data:

$existingBookings = [
    ['start' => 1715000000, 'end' => 1715003600], // 1 hour slot
    ['start' => 1715007200, 'end' => 1715010800], // Another 1 hour slot
];

$requestedBooking = ['start' => 1715002000, 'end' => 1715005000]; // This overlaps the first one!

Your function should correctly identify that the $requestedBooking above is invalid.