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).

enter image description here

You only check for one specific timepoint `2014-10-06 07:00:00` and this is between `2014-10-06 06:00:00` and `2014-10-06 14:00:00`. So everything works correct. But you have to check the begin and end date of the planned reservation. — TiMESPLiNTER, Oct 06 '14 at 09:25
@TiMESPLiNTER i think i understand your concept, i just don't know how should i do it. Do i have check the begin and end date of the planned reservation against every hour range? — Jeremy, Oct 06 '14 at 09:29

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

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.

edited May 23 '17 at 12:11

Community

1
1

answered Oct 06 '14 at 09:37

TiMESPLiNTER

5,741
2
28
64

Thanks! I'll give it a try. And thanks again for the suggestion, the variable has been sanitized prior to query making. – Jeremy Oct 06 '14 at 09:47
I run tested your query, and strangely it returns with this: 9, 2014-10-15 07:00:00, 2014-10-15 17:00:00, 1, 5 and this 10, 2014-10-07 07:00:00, 2014-10-07 09:00:00, 1, 5 – Jeremy Oct 06 '14 at 10:14
Okay, I mistyped something. Now check the sqlfiddle. – TiMESPLiNTER Oct 06 '14 at 10:42

Checking Resources Reservation Availability

1 Answers1