如何显示数据库中不存在的所有列表记录事件？ mysql php

Question

I have a list of rooms

1. Deluxe
2. Standard
3. Regular

I made a display records based on what the user added per day.

If the user add a new event and use one of the room.

Example: for this day August 26,2018. one event was booked, and it will display the ff. Retrieved from database.

Deluxe - 1 booked

What I want to display is this:

Deluxe - 1 booked

Standard - none

Regular - none

I want to add all the list of rooms even some of the rooms has no value in database or doesn't exist in database.

But how can I do it? Please help!

My sample code:

    $connect = mysqli_connect("localhost", "root", "", "bookings");
    $sql = "SELECT * FROM events WHERE (start BETWEEN '$start' AND '$end' OR 
    end BETWEEN '$start' AND '$end');  
    $result = mysqli_query($connect, $sql);
    while($rows = mysqli_fetch_array($result))
    {
    echo $rows['room'];
    }

Answer 1

Based on the OP's comments, you should get count of booked rooms per category using a GROUP BY clause. If there is no entry found for a particular room category, then you should let your application code handle this. Here is a sample code:

$connect = mysqli_connect("localhost", "root", "", "bookings");
$sql = "SELECT room, COUNT(id) as rooms_booked 
         FROM events 
         WHERE ((start BETWEEN '$start' AND '$end') OR 
                (end BETWEEN '$start' AND '$end')
               ) 
         GROUP BY room";  
$result = mysqli_query($connect, $sql);

// all possible room categories
$all_room_categories = array('Deluxe', 'Standard', 'Regular');
// room categories for which there is some booking done
$booked_room_categories = array();

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
    echo $row['room'] . " - " . $row['rooms_booked'] . " booked" . "<\br>";
    $booked_room_categories[] = $row['room'];
}

// Now find all the room categories for which no booking was found
$unbooked_room_categories = array_diff($all_room_categories, $booked_room_categories);

 // Loop over unbooked rooms to print their status as well
foreach ($unbooked_room_categories as $cat)
{
    echo $cat . " - " . " none" . "<\br>";
}