基于MySQL+PHP查询场馆可用时间段的技术实现问询

Here's a practical solution to compute available time slots for your venue, considering booking status and space type rules. We'll use MySQL to fetch booked data and PHP to process it into the desired available slot format.

Business Rules Recap #

• Venue operates from 07:00:00 to 21:00:00
• full: Occupies entire space (cannot be shared)
• half: Uses half the space (supports 2 parties total)
• shared: Uses 1/4 of the space (supports 4 parties total)

Step 1: Database Schema & Test Data #

First, set up your tables and insert sample booking data:

Table Creation SQL:

CREATE TABLE times ( 
  id integer UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  name VARCHAR(30) NOT NULL, 
  space tinyint NOT NULL COMMENT '4 = full, 2 = half, 1 = shared', 
  hall_id INT(6) UNSIGNED 
);

CREATE TABLE times_meta ( 
  id integer UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  start_date date NOT NULL, 
  end_date date NOT NULL, 
  start_time time NOT NULL, 
  end_time time NOT NULL, 
  time_id INT(6) UNSIGNED, 
  FOREIGN KEY (time_id) REFERENCES times(id) 
);

Test Data Insert:

INSERT INTO times (name, space, hall_id) 
VALUES 
  ('Booked by User 1', '4', '1'), 
  ('Booked by User 2', '4', '1'), 
  ('Booked by User 3', '4', '1'), 
  ('Booked by User 4', '2', '1'), 
  ('Booked by User 5', '4', '1'), 
  ('Booked by User 5', '1', '1');

INSERT INTO times_meta (start_date, end_date, start_time, end_time, time_id) 
VALUES 
  ('2019-09-20', '2019-09-20', '07:00:00', '10:00:00', '1'), 
  ('2019-09-20', '2019-09-20', '10:00:00', '11:00:00', '2'), 
  ('2019-09-20', '2019-09-20', '13:00:00', '17:00:00', '3'), 
  ('2019-09-20', '2019-09-20', '17:00:00', '18:00:00', '4'), 
  ('2019-09-20', '2019-09-20', '18:00:00', '19:00:00', '5'), 
  ('2019-09-20', '2019-09-20', '19:00:00', '21:00:00', '6');

Step 2: Fetch Booked Slots with MySQL #

Use this query to retrieve all booked slots for your target date and venue:

SELECT 
  TM.start_time, 
  TM.end_time, 
  CASE T.space 
    WHEN 4 THEN 'full' 
    WHEN 2 THEN 'half' 
    WHEN 1 THEN 'shared' 
  END AS space_type 
FROM times AS T 
JOIN times_meta AS TM ON T.id = TM.time_id 
WHERE T.hall_id = 1 AND TM.start_date = '2019-09-20' 
ORDER BY TM.start_time ASC;

Sample Query Results:

+------------+----------+------------+
| start_time | end_time | space_type |
+------------+----------+------------+
| 07:00:00   | 10:00:00 | full       |
| 10:00:00   | 11:00:00 | full       |
| 13:00:00   | 17:00:00 | full       |
| 17:00:00   | 18:00:00 | half       |
| 18:00:00   | 19:00:00 | full       |
| 19:00:00   | 21:00:00 | shared     |
+------------+----------+------------+

Step 3: Calculate Available Slots with PHP #

This PHP script processes the booked data to identify gaps and determine allowed space types based on remaining capacity:

<?php
// Configure your database connection
$dbHost = 'localhost';
$dbName = 'your_database';
$dbUser = 'your_username';
$dbPass = 'your_password';

try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Define parameters
$targetDate = '2019-09-20';
$hallId = 1;
$venueOpen = '07:00:00';
$venueClose = '21:00:00';
$totalCapacity = 4; // Represents full space (4 = full, 2=half, 1=shared)

// Fetch booked slots with numeric capacity values
$stmt = $pdo->prepare("
    SELECT TM.start_time, TM.end_time, T.space AS capacity_used
    FROM times T
    JOIN times_meta TM ON T.id = TM.time_id
    WHERE T.hall_id = ? AND TM.start_date = ?
    ORDER BY TM.start_time ASC
");
$stmt->execute([$hallId, $targetDate]);
$bookedSlots = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Create timeline events (start/end of bookings + venue open/close)
$events = [];
$events[] = ['time' => $venueOpen, 'change' => 0];
foreach ($bookedSlots as $slot) {
    $events[] = ['time' => $slot['start_time'], 'change' => $slot['capacity_used']];
    $events[] = ['time' => $slot['end_time'], 'change' => -$slot['capacity_used']];
}
$events[] = ['time' => $venueClose, 'change' => 0];

// Sort events by time
usort($events, function($a, $b) {
    return strtotime($a['time']) - strtotime($b['time']);
});

// Process events to find available slots
$availableSlots = [];
$currentUsed = 0;
$prevTime = null;

foreach ($events as $event) {
    $currTime = $event['time'];
    
    if ($prevTime && strtotime($prevTime) < strtotime($currTime)) {
        $remaining = $totalCapacity - $currentUsed;
        $allowedTypes = [];
        
        if ($remaining >= 4) $allowedTypes[] = 'full';
        if ($remaining >= 2) $allowedTypes[] = 'half';
        if ($remaining >= 1) $allowedTypes[] = 'shared';
        
        if (!empty($allowedTypes)) {
            $availableSlots[] = [
                'start' => $prevTime,
                'end' => $currTime,
                'types' => $allowedTypes
            ];
        }
    }
    
    $currentUsed += $event['change'];
    $prevTime = $currTime;
}

// Output the results
echo "Available:\n";
foreach ($availableSlots as $slot) {
    $typeStr = implode(' / ', $slot['types']);
    echo "{$slot['start']} - {$slot['end']} - {$typeStr}\n";
}
?>

Expected Output #

When you run the script, you'll get this formatted output:

Available:
11:00:00 - 13:00:00 - full / half / shared
17:00:00 - 18:00:00 - half / shared
19:00:00 - 21:00:00 - half / shared

Note: The original expected output simplified the 11-13 slot to just "full", but since all space is available, full, half, and shared are all valid options. The script correctly lists all allowed types based on remaining capacity.

内容的提问来源于stack exchange，提问作者Aayush Dahal