Checking Resources Reservation Availability

Question

i have the following table:

id | rsvp_date | return_date | user_id | car_id

where ID is the PK, rsvp_date & return_date is DATETIME, user_id & car_id is FK to user table and car table respectively.

What i want to do is to check everytime a user would like to make a reservation. The Query that checks that boils down to this (this query get's called by a function checkAvailability():

SELECT id
FROM `car_reservation`
WHERE ('2014-10-06 07:00:00' BETWEEN rsvp_date AND return_date)
AND car_id = 5;

The function i mentioned:

function checkAvailability($datetime, $id = null){
    $sql = "SELECT id
            FROM `car_reservation`
            WHERE ('".$datetime."' BETWEEN rsvp_date AND return_date)
            AND car_id = ".$id;

    $query = $this->db->query($sql);
    // echo "<pre>"; print_r($this->db->last_query());die();        
    if($query->num_rows() > 0){
        return FALSE;
    }else{
        return TRUE;
    }
}

Where the given date will be matched against rsvp_date and return_date columns. And the date is taken from a field in the form (there are 2 field; Start Date and Return Date).

The current code does not work as i want it to be. For example:

• Guest #1: Reserve Car A between 2014-10-06 07:00:00 To 2014-10-06 10:00:00 [Valid]
• Guest #2: Reserve Car B between 2014-10-06 07:00:00 To 2014-10-06 10:00:00 [Valid]
• Guest #3: Reserve Car A between 2014-10-06 08:00:00 To 2014-10-06 11:30:00 [Unavailable, due to start time is not available]
• Guest #4: Reserve Car B between 2014-10-06 06:00:00 To 2014-10-06 14:00:00 [Valid, but it is not suppose to be. Since Guest #2 already booked the car between 07:00:00 - 10:00:00].

Can anyone help me on how should i improve my code, so that the last condition will return False when there is already a booking made in between the requested reservation?

PS: I found this article but having a difficulty understanding it, i am looking for a similar approach only simpler. http://www.codeproject.com/Articles/168662/Time-Period-Library-for-NET

EDIT:

This picture visualize everything, i think now i just need to implement a function as specified on each column header (those who has check mark).

Answer 1

According to this SO question you have to check different:

$query = "
    SELECT id
    FROM `car_reservation`
    WHERE (
        '".$datetimeStart."' <= return_date 
        AND 
        '".$datetimeEnd."' >= rsvp_date 
    ) AND car_id = ".$id
";

See SQL fiddle: http://sqlfiddle.com/#!2/d818c/1

Where $datetimeStart is 2014-10-06 06:00:00 and $datetimeEnd is 2014-10-06 14:00:00 as of your Guest #4 test case scenario.

And be aware of SQL injections if you concatinate the SQL query with variables which may contain user input.