I'm making a room reservation system in PHP and mySQL.

                                <div >
                                    <label for="from" >From</label>
                                    <select required name="from" id="from" >
                                        <option selected disabled value="">Pick a time</option>
                                        <option value="08:00:00">8 a.m.</option>
                                        <option value="09:00:00">9 a.m.</option>
                                        <option value="10:00:00">10 a.m.</option>
                                        <option value="11:00:00">11 a.m.</option>
                                        <option value="12:00:00">12 p.m.</option>
                                        <option value="13:00:00">1 p.m.</option>
                                        <option value="14:00:00">2 p.m.</option>
                                        <option value="15:00:00">3 p.m.</option>
                                        <option value="16:00:00">4 p.m.</option>
                                        <option value="17:00:00">5 p.m.</option>
                                        <option value="18:00:00">6 p.m.</option>
                                        <option value="19:00:00">7 p.m.</option>
                                        <option value="20:00:00">8 p.m.</option>
                                        <option value="21:00:00">9 p.m.</option>
                                    </select>
                                </div>
                                <div >
                                    <label for="to" >To</label>
                                    <select required name="to" id="to" >
                                        <option selected disabled value="">Pick a time</option>
                                        <option value="09:00:00">9 a.m.</option>
                                        <option value="10:00:00">10 a.m.</option>
                                        <option value="11:00:00">11 a.m.</option>
                                        <option value="12:00:00">12 p.m.</option>
                                        <option value="13:00:00">1 p.m.</option>
                                        <option value="14:00:00">2 p.m.</option>
                                        <option value="15:00:00">3 p.m.</option>
                                        <option value="16:00:00">4 p.m.</option>
                                        <option value="17:00:00">5 p.m.</option>
                                        <option value="18:00:00">6 p.m.</option>
                                        <option value="19:00:00">7 p.m.</option>
                                        <option value="20:00:00">8 p.m.</option>
                                        <option value="21:00:00">9 p.m.</option>
                                        <option value="22:00:00">10 p.m.</option>
                                    </select>
                                </div>

This is the HTML code for my select box. In my database, I have a field called start and end that's supposed to supposed to store the start and end time of booking for a room. I was wondering if there is a way to compare the values of the options with the database and disable the times that are already booked

CodePudding user response：

You have to change your static web page to a dynamic one using PHP and a MySQL database.

At first, I created a database called test with a single table booking and the fields id (int), arrival (datetime) and departure (datetime).

I added some values in it :

Then, you will need to connect to your database :

$servername = "localhost";
$username = "root";
$password = "";
$database = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

Once you are successfully connected, you need a SELECT statement to get arrivals and departures from database :

$sql = "SELECT arrival, departure FROM booking";
$result = $conn->query($sql);

Finally, you have to fetch datas and to add all arrivals in an array (then you should do the same for departures but I will show you for arrivals as you only need to copy / paste).

$opening_hour = 8;
$closure_hour = 21;

$arrivals = [];

if ($result->num_rows > 0) {

  // output data of each row
  while($row = $result->fetch_assoc()) {
  
    $arrival_hour = date_parse($row["arrival"])['hour'];
  
    $arrivals[] = $arrival_hour;
  
  }
} 

$conn->close();

As you loop through the arrivals array, you need to set select's options as disabled if you can find them out into the array.

    <label for="from">Arrival time :</label>
    <select required name="from" id="from" >

        <option selected disabled value="">Pick a time</option>

        <?php 

        for ($hour = $opening_hour; $hour <= $closure_hour; $hour  ) {

            if(in_array($hour, $arrivals)) {

                echo "<option disabled value='" . $hour . "'>" . $hour . "</option>";

            } else {

                echo "<option value='" . $hour . "'>" . $hour . "</option>";
            }


        }

        ?>

    </select>

Here is the final result :