How can exclusion constraints be used in PHP to prevent overbooking in a database?

Exclusion constraints in PHP can be used to prevent overbooking in a database by ensuring that conflicting bookings cannot occur at the same time for a specific resource. This can be achieved by defining an exclusion constraint on the database table that prevents overlapping time intervals for bookings. 

// Example PHP code snippet to create an exclusion constraint for preventing overbooking in a database

// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');

// Create a table for bookings with start and end time columns
$pdo->exec("CREATE TABLE bookings (
    id INT PRIMARY KEY,
    resource_id INT,
    start_time DATETIME,
    end_time DATETIME,
    EXCLUDE USING GIST (resource_id WITH =, tsrange(start_time, end_time) WITH &&)
)");

// Insert a new booking
$stmt = $pdo->prepare("INSERT INTO bookings (id, resource_id, start_time, end_time) VALUES (?, ?, ?, ?)");
$stmt->execute([1, 1, '2022-01-01 10:00:00', '2022-01-01 12:00:00']);

// Try to insert a conflicting booking
$stmt = $pdo->prepare("INSERT INTO bookings (id, resource_id, start_time, end_time) VALUES (?, ?, ?, ?)");
$stmt->execute([2, 1, '2022-01-01 11:00:00', '2022-01-01 13:00:00']); // This will fail due to the exclusion constraint

Keywords

exclusion constraints overbooking prevention PHP database error handling

Related Questions