Room Management

The room management system provides the foundation for the reservation system, maintaining the catalog of available rooms and their associated materials.

Room Model

The Room model handles all database operations related to rooms:

// From app/models/Room.php:5-12
class Room
{
    private \PDO $db;
    
    public function __construct()
    {
        $this->db = Database::getConnection();
    }
}

Retrieving Rooms

The system provides methods to query rooms from the database:

Get All Rooms

// From app/models/Room.php:17-24
public function getAll(): array
{
    $sql = "SELECT id, name FROM rooms ORDER BY name";
    $stmt = $this->db->prepare($sql);
    $stmt->execute();
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Returns: Array of all rooms sorted alphabetically by name.

Usage
Example Output

$roomModel = new Room();
$rooms = $roomModel->getAll();

foreach ($rooms as $room) {
    echo $room['name'];
}

[
    ['id' => 1, 'name' => 'Auditorio Principal'],
    ['id' => 2, 'name' => 'Sala de Conferencias'],
    ['id' => 3, 'name' => 'Sala de Juntas']
]

The getAll() method is commonly used to populate room selection dropdowns in the reservation form.

Find Room by ID

// From app/models/Room.php:29-39
public function findById(int $id): ?array
{
    $sql = "SELECT id, name FROM rooms WHERE id = :id LIMIT 1";
    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
    
    $room = $stmt->fetch(PDO::FETCH_ASSOC);
    
    return $room ?: null;
}

Returns: Room data array or null if not found.

Return Value

// Success
[
    'id' => 2,
    'name' => 'Sala de Conferencias'
]

// Not found
null

Room-Material Association

Rooms are associated with materials through the room_materials join table. Each room can have multiple materials, and each material can belong to multiple rooms.

Get Room Materials

// From app/models/Room.php:45-63
public function getMaterials(int $roomId): array
{
    $sql = "SELECT 
                m.id,
                m.name,
                rm.quantity
            FROM room_materials rm
            JOIN materials m ON m.id = rm.material_id
            WHERE rm.room_id = :room_id
            ORDER BY m.name";
    
    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':room_id', $roomId, PDO::PARAM_INT);
    $stmt->execute();
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Returns: Array of materials available in the specified room, including quantity information.

Usage
Example Output

$roomModel = new Room();
$materials = $roomModel->getMaterials(2);

foreach ($materials as $material) {
    echo "{$material['name']} (Qty: {$material['quantity']})";
}

[
    [
        'id' => 1,
        'name' => 'Proyector',
        'quantity' => 1
    ],
    [
        'id' => 3,
        'name' => 'Micrófono',
        'quantity' => 2
    ],
    [
        'id' => 5,
        'name' => 'Pizarra',
        'quantity' => 1
    ]
]

This method is used to display available materials when users create reservations for a specific room.

Room Data Structure

The room database table has a simple structure:

CREATE TABLE rooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
)

Basic Room Object

[
    'id' => 1,
    'name' => 'Auditorio Principal'
]

Room with Materials

[
    'id' => 2,
    'name' => 'Sala de Conferencias',
    'materials' => [
        ['id' => 1, 'name' => 'Proyector', 'quantity' => 1],
        ['id' => 3, 'name' => 'Micrófono', 'quantity' => 2]
    ]
]

Room Selection in Reservations

When creating a reservation, rooms are loaded into the form for user selection:

Load room list

$roomModel = new Room();
$rooms = $roomModel->getAll();

Display in form

<select name="room_id" required>
    <option value="">Seleccionar sala...</option>
    <?php foreach ($rooms as $room): ?>
        <option value="<?= $room['id'] ?>">
            <?= htmlspecialchars($room['name']) ?>
        </option>
    <?php endforeach; ?>
</select>

Load materials for selected room

When user selects a room (via JavaScript), load associated materials:

$materials = $roomModel->getMaterials($_POST['room_id']);

Room Availability Checking

While the Room model itself doesn’t handle availability, the ReservationSlot model provides methods to check room availability:

Check for Conflicts

// From app/models/ReservationSlot.php:59-85
public function hasConflict(
    int $roomId,
    string $date,
    string $startTime,
    string $endTime
): bool {
    $sql = "SELECT 1
            FROM reservation_slots rs
            JOIN reservations r ON r.id = rs.reservation_id
            WHERE r.room_id = :room_id
              AND rs.date = :date
              AND (rs.start_time < :end_time 
                   AND rs.end_time > :start_time)
            LIMIT 1";
    
    return (bool) $stmt->fetchColumn();
}

Always check for conflicts before creating a reservation to prevent double-booking:

// From app/controllers/ReservationController.php:86-90
if ($slotModel->hasConflict((int)$roomId, $date, $start, $end)) {
    throw new Exception("Conflicto de horario el $date de $start a $end.");
}

$slotModel->create($reservationId, $date, $start, $end);

Get Occupied Time Slots

// From app/models/ReservationSlot.php:91-108
public function getOccupiedSlots(int $roomId, string $date): array
{
    $sql = "SELECT rs.start_time, rs.end_time
            FROM reservation_slots rs
            JOIN reservations r ON r.id = rs.reservation_id
            WHERE r.room_id = :room_id
              AND rs.date = :date
            ORDER BY rs.start_time";
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Returns: List of all occupied time slots for a specific room and date.

Usage
Example Output

$slotModel = new ReservationSlot();
$occupied = $slotModel->getOccupiedSlots(2, '2026-03-15');

// Display availability calendar
foreach ($occupied as $slot) {
    echo "Occupied: {$slot['start_time']} - {$slot['end_time']}";
}

[
    ['start_time' => '09:00', 'end_time' => '11:00'],
    ['start_time' => '14:00', 'end_time' => '16:00'],
    ['start_time' => '18:00', 'end_time' => '20:00']
]

Use this method to build availability calendars showing when a room is free or occupied.

Room Information in Reservations

Room information is included in reservation queries through SQL joins:

// From app/models/Reservation.php:45-60
$sql = "SELECT 
            r.id,
            r.user_id,
            r.room_id,
            r.event_name,
            r.status,
            r.notes,
            r.created_at,
            u.username,
            rm.name AS room_name  -- Room name joined
        FROM reservations r
        JOIN users u ON u.id = r.user_id
        JOIN rooms rm ON rm.id = r.room_id
        WHERE r.id = :id
        LIMIT 1";

This ensures reservation objects always include the room name for display purposes.

Room Management Workflow

The typical workflow for using rooms in the application:

Browse available rooms

User views list of all rooms in the system.

$rooms = $roomModel->getAll();

Select a room

User chooses a room for their reservation.

View room materials

System displays materials available for that room.

$materials = $roomModel->getMaterials($roomId);

Check availability

User selects date/time and system checks for conflicts.

$hasConflict = $slotModel->hasConflict($roomId, $date, $start, $end);

Create reservation

If available, reservation is created with the selected room.

$reservationId = $reservationModel->create($userId, $roomId, $event, $notes);

Room Queries in Reservation Context

Rooms are frequently queried in the context of reservations:

Reservation List with Room Names

// From app/models/Reservation.php:78-90
$sql = "SELECT 
            r.id,
            r.event_name,
            r.status,
            r.created_at,
            rm.name AS room_name,
            u.username
        FROM reservations r
        JOIN rooms rm ON rm.id = r.room_id
        JOIN users u ON u.id = r.user_id
        ORDER BY r.created_at DESC";

Room names are joined to provide context in reservation listings.

User's Reservations with Rooms

// From app/models/Reservation.php:214-224
$sql = "SELECT
            r.id,
            r.event_name,
            r.status,
            r.created_at,
            rm.name AS room_name
        FROM reservations r
        JOIN rooms rm ON rm.id = r.room_id
        WHERE r.user_id = :user_id
        ORDER BY r.created_at DESC";

Users see which rooms they’ve reserved in their dashboard.

Extending Room Functionality

While the current implementation is minimal, the Room model can be extended to include:

Room capacity
Room type/category
Operating hours
Location/building information
Photos or virtual tours
Special requirements or restrictions

The simple structure makes it easy to add new fields to the rooms table and corresponding getter methods in the model.

Common Room Operations

List All Rooms
Get Room Details
Get Room with Materials
Check Availability

$roomModel = new Room();
$rooms = $roomModel->getAll();

Use for: Dropdown menus, room directories

$roomModel = new Room();
$room = $roomModel->findById($id);

if ($room) {
    echo $room['name'];
}

Use for: Displaying room information

$roomModel = new Room();
$room = $roomModel->findById($id);
$materials = $roomModel->getMaterials($id);

$roomData = array_merge($room, ['materials' => $materials]);

Use for: Complete room information pages

$slotModel = new ReservationSlot();
$occupied = $slotModel->getOccupiedSlots($roomId, $date);

// Calculate free slots based on occupied
$freeSlots = calculateFreeSlots($occupied);

Use for: Availability calendars

Get Started

Core Features

User Guides

Architecture

Room Model

Retrieving Rooms

Get All Rooms

Find Room by ID

Room-Material Association

Get Room Materials

Room Data Structure

Basic Room Object

Room with Materials

Room Selection in Reservations

Room Availability Checking

Check for Conflicts

Get Occupied Time Slots

Room Information in Reservations

Room Management Workflow

Room Queries in Reservation Context

Extending Room Functionality

Common Room Operations

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Architecture

​Room Model

​Retrieving Rooms

​Get All Rooms

​Find Room by ID

​Room-Material Association

​Get Room Materials

​Room Data Structure

​Basic Room Object

​Room with Materials

​Room Selection in Reservations

​Room Availability Checking

​Check for Conflicts

​Get Occupied Time Slots

​Room Information in Reservations

​Room Management Workflow

​Room Queries in Reservation Context

​Extending Room Functionality

​Common Room Operations

Build docs developers (and LLMs) love

Room Model

Retrieving Rooms

Get All Rooms

Find Room by ID

Room-Material Association

Get Room Materials

Room Data Structure

Basic Room Object

Room with Materials

Room Selection in Reservations

Room Availability Checking

Check for Conflicts

Get Occupied Time Slots

Room Information in Reservations

Room Management Workflow

Room Queries in Reservation Context

Extending Room Functionality

Common Room Operations