Project: an appointment booking system

CREATE TABLE services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  minutes INT NOT NULL
);

CREATE TABLE bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code CHAR(6) NOT NULL UNIQUE,
  service_id INT NOT NULL,
  customer VARCHAR(100) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  starts_at DATETIME NOT NULL,
  status ENUM('booked','done','cancelled') NOT NULL DEFAULT 'booked',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (service_id) REFERENCES services(id),
  UNIQUE KEY one_booking_per_slot (starts_at)          -- the database itself prevents double booking
);

INSERT INTO services (name, price, minutes) VALUES
  ('Haircut', 300, 30), ('Braiding', 2500, 120), ('Manicure', 800, 60);

<?php
function slots(string $date, string $open = '08:00', string $close = '17:00', int $step = 30): array {
    $out = [];
    $t = strtotime("$date $open");
    $end = strtotime("$date $close");
    while ($t < $end) {
        $out[] = date('H:i', $t);
        $t += $step * 60;
    }
    return $out;
}

function freeSlots(string $date, array $booked): array {
    $all = slots($date);
    return array_values(array_diff($all, $booked));
}

// pretend these came from: SELECT TIME_FORMAT(starts_at, '%H:%i') FROM bookings WHERE DATE(starts_at) = ? AND status = 'booked'
$booked = ['09:00', '09:30', '13:00'];
$free = freeSlots('2026-10-05', $booked);
echo count($free), " free slots on 5 Oct\n";
echo implode(' ', array_slice($free, 0, 6)), " ...\n";

function bookingCode(): string {
    $chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';     // no 0/O or 1/I to avoid confusion
    $code = '';
    for ($i = 0; $i < 6; $i++) $code .= $chars[random_int(0, strlen($chars) - 1)];
    return $code;
}
echo "Your booking code: ", bookingCode(), "\n";

function validPhone(string $p): ?string {
    $d = preg_replace('/\D/', '', $p);
    if (preg_match('/^254([17]\d{8})$/', $d, $m)) $d = '0' . $m[1];
    return preg_match('/^0[17]\d{8}$/', $d) ? $d : null;
}
var_dump(validPhone('+254 712 345 678'), validPhone('12345'));

<?php
require __DIR__ . '/db.php';
$phone = validPhone($_POST['phone'] ?? '');
$name = trim($_POST['name'] ?? '');
$serviceId = (int)($_POST['service_id'] ?? 0);
$start = ($_POST['date'] ?? '') . ' ' . ($_POST['time'] ?? '') . ':00';

if (!$phone || $name === '' || !strtotime($start) || strtotime($start) < time()) {
    exit('Please check your details and choose a future time.');
}
try {
    $code = bookingCode();
    $stmt = $pdo->prepare('INSERT INTO bookings (code, service_id, customer, phone, starts_at) VALUES (?, ?, ?, ?, ?)');
    $stmt->execute([$code, $serviceId, $name, $phone, $start]);
    echo "Booked! Your code is $code. We'll see you on " . date('D d M, g:i a', strtotime($start));
} catch (PDOException $e) {
    if ($e->errorInfo[1] === 1062) {            // MySQL "duplicate key": slot just taken
        exit('Sorry, that time was just booked. Please choose another.');
    }
    throw $e;
}

<?php
function slotsForDay(string $open, string $close, int $stepMinutes, int $durationMinutes, array $booked, array $breaks = []): array {
    $slots = [];
    $start = strtotime("2026-10-05 $open");
    $end = strtotime("2026-10-05 $close");
    for ($t = $start; $t + $durationMinutes * 60 <= $end; $t += $stepMinutes * 60) {
        $slotStart = $t;
        $slotEnd = $t + $durationMinutes * 60;
        $clash = false;
        foreach (array_merge($booked, $breaks) as [$bStart, $bEnd]) {
            $bs = strtotime("2026-10-05 $bStart");
            $be = strtotime("2026-10-05 $bEnd");
            if ($slotStart < $be && $slotEnd > $bs) { $clash = true; break; }   // overlap test
        }
        if (!$clash) $slots[] = date('H:i', $slotStart);
    }
    return $slots;
}

$booked = [['09:00', '10:30'], ['14:00', '15:00']];
$breaks = [['13:00', '14:00']];
echo "60-minute service: ", implode(', ', slotsForDay('08:00', '17:00', 30, 60, $booked, $breaks)), "\n";
echo "2-hour service:    ", implode(', ', slotsForDay('08:00', '17:00', 30, 120, $booked, $breaks)), "\n";

-- One booking per staff member per start time
ALTER TABLE bookings ADD UNIQUE KEY uniq_staff_slot (staff_id, booking_date, start_time);

$pdo->beginTransaction();
$stmt = $pdo->prepare(
    'SELECT COUNT(*) FROM bookings
     WHERE staff_id = ? AND booking_date = ? AND status <> "cancelled"
       AND start_time < ? AND end_time > ? FOR UPDATE'
);
$stmt->execute([$staffId, $date, $newEnd, $newStart]);
if ($stmt->fetchColumn() > 0) {
    $pdo->rollBack();
    exit('Sorry, that time was just taken. Please choose another slot.');
}
$pdo->prepare('INSERT INTO bookings (staff_id, booking_date, start_time, end_time, customer_name, phone, status) VALUES (?, ?, ?, ?, ?, ?, "pending")')
    ->execute([$staffId, $date, $newStart, $newEnd, $name, $phone]);
$pdo->commit();

pending  →  confirmed (deposit paid or owner approves)  →  completed
    ↘ cancelled (by customer or owner)          ↘ no_show

# cron: send tomorrow's reminders at 8:15 every morning
15 8 * * * /usr/bin/php /home/username/app/cron/send_reminders.php >> /home/username/logs/reminders.log 2>&1