CODEWITHSUNDEEP
phpmysqlpdo
StuBee
StuBee

Reputation: 89

Check value between two times in my database php

I have a booking form where the users can book a certain facility, on a certain date and between two times. I have come to a point where my PHP code will prevent the user from booking the same pitch on the same date and from the same start or end time.

However the user can go between a time and book the same facility on the same date as another user. e.g. if one user has booked from 9 to 12 on the 1st May another user can come in and book the 10 or 11 slot.

Is there any way of preventing the user from booking a time between the time another user has already booked???

    <?php
        include "config.php"; 
        //Booking point
        if(isset($_POST['booking']))
        {
            //get values for variables
            $pitchID = $_POST['pitchID'];
            $start_date = $_POST['start_date'];
            $start_hour = $_POST['start_hour'];
            $end_hour = $_POST['end_hour'];
            $booking_age = $_POST['booking_age'];
            $pitch_size = $_POST['pitch_size'];
            $light_tokens = $_POST['light_tokens'];


/*          $q = $db->prepare("SELECT * FROM booking WHERE start_date = ?, start_hour = ?, end_hour=?, pitchID=?");
            $query = $q->execute(array($start_date, $start_hour, $end_hour, $pitchID));
            $count = $q->rowCount(); */
                $q = $db->prepare("SELECT * FROM booking WHERE start_date = ? AND start_hour = ? AND pitchID = ?");
                $query = $q->execute(array($start_date, $start_hour, $pitchID));
                $count = $q->rowCount();

                if($count == 0){

                    $query = $db->prepare("INSERT INTO booking SET pitchID = ?, start_date = ?, start_hour = ?, end_hour = ?, booking_age = ?, pitch_size = ?, light_tokens = ?, userID='$userID'");
                    $query = $query->execute(array($pitchID,$start_date,$start_hour,$end_hour,$booking_age,$pitch_size,$light_tokens));

                    if($query){
                        echo "Your booking has been made";
                        header("Location:home2_template.html");
                        return; 
                    } else {
                        echo "Fail";
                    } //else fail   
                } else {
                    echo "This booking already exists"; 
                } //else count 
        } //if booking
    ?>

Upvotes: 0

Views: 638

Answers (1)

jpintor
jpintor

Reputation: 419

If you only can reserve one day, try this

select * from booking
where (start_date='new_date' 
        and ( 
                (start_hour <='new_start_hour' and end_hour>='new_start_hour') 
                or
                (start_hour<= 'new_end_hour' and end_hour>='new_end_hour')
            )
    ) and pitchID = ?

Upvotes: 1

Related Questions