Avoiding date conflicts? (For Booking/Reservation system)

[bachx]

We're doing some kind of a reservation system where a customer can rent an office for a specific period of time. It's very basic, the structure should like something like this:

 

---------------------------------------------------------------

Customer Name      ::    Starting Time      ::    Ending Time

---------------------------------------------------------------

John Smith        ::    03-09-2009 21:00    ::    03-12-2009 12:00

Alex John          ::    03-17-2009 11:00    ::    03-20-2009 17:00

David Smith      ::    03-22-2009 05:00    ::    03-24-2009 22:00

---------------------------------------------------------------

 

The above dates are not conflicting or spanning over each other, and that's how I plan to keep it.

 

Now when entering a new value to the table, for example:

Starting Date: 03-15-2009

Ending Date: 03-22-2009

 

Although no one is booked at 15th of March, it should give an error because the above date range conflicts with Alex John's reservation that starts at the 17th.

 

Any ideas how to implement such a thing?

 

Thanks in advance.

[Psycho]

Assuming this is in a database, it's quite simple:

 

//Variables to be tesed
$new_start_date = $_POST['new_start_date'];
$new_end_date = $_POST['new_end_date'];

$query = "SELECT * FROM reservations
          WHERE ($new_start_date <= 'end_date' AND $new_end_date >= 'start_date')
             OR ($new_start_date <= 'end_date' AND $new_end_date >= 'start_date')";

$result = mysql_query($query);

if (mysql_num_rows($result)>0)
{
    echo "There is a conflict";
}
else
{
    echo "No conflict";
}

[redarrow]

can you please, tell me for education purpose why the where clause, and the or are the same please, interesting cheers mate.

 

op.

 

also add the

 

or die(mysql_error()); <<< on the end of the mysql_query

[Psycho]

Ahh. When I started writing that code I was in the minset of the conflict could either be overlapping the beginning or ending of a current registration. As I wrapped my head around the clauses and started revising them I didn't realize I came up with the same thing which handles every eventuality.

 

So, all that is needed is this

$query = "SELECT * FROM reservations
          WHERE $new_start_date <= 'end_date' AND $new_end_date >= 'start_date'";

 

also add the

 

or die(mysql_error()); <<< on the end of the mysql_query

 

You are correct, but I'm not going to always add ALL the details not specific to the solution. For example, youwould also want to use mysql_real_escape_string() on the POST values as well. I leave it up to the OP to implement all the other necessities.

[bachx]

Thanks mjdamato, I'll try it up.

 

Just a quick question. When you say "<= end_date" and ">= start_date" in your query, does that mean it's choosing the maximum end_time and minimum start_time field values and comparing them to the values you entered?

 

What if someone made a reservation, say from March 15th to March 16th, no conflict here so I'm assuming your query will work normally right?

 

Thanks again.

[samshel]

Ahh. When I started writing that code I was in the minset of the conflict could either be overlapping the beginning or ending of a current registration. As I wrapped my head around the clauses and started revising them I didn't realize I came up with the same thing which handles every eventuality.

 

So, all that is needed is this

$query = "SELECT * FROM reservations
          WHERE $new_start_date <= 'end_date' AND $new_end_date >= 'start_date'";

 

also add the

 

or die(mysql_error()); <<< on the end of the mysql_query

 

You are correct, but I'm not going to always add ALL the details not specific to the solution. For example, youwould also want to use mysql_real_escape_string() on the POST values as well. I leave it up to the OP to implement all the other necessities.

 

please correct me if i am wrong, but this will cover only if the new period is completely within the old period and not the overlap(if start a new period is within old, but end is out of it, this should also be not allowed)

 

i think this will work..

 

$query = "SELECT * FROM reservations
          WHERE (
                  ($new_start_date >= 'start_date' AND $new_start_date <= 'end_date') OR 
                  ($new_end_date >= 'start_date' AND $new_end_date <= 'end_date')
";

[Psycho]

please correct me if i am wrong

 

I will, and you are (I'm pretty sure).

 

There are six different scenarios. Let's create some examples for illustration purposes. Let's assume a current registration period in the DB is Jan 3 to Jan 7. Here are examples of the six different scenarios.

 

1-1 to 1-2 (OK) Exist before current period

1-1 to 1-5 (Error) Overlaps the beginning of current

1-1 to 1-9 (Error) Completely overlaps the current

1-4 to 1-6 (Error) Completely within current

1-5 to 1-9 (Error) Overlaps the end of current

1-8 to 1-9 (OK) Exists after current period

 

So, now let's apply the test of

WHERE $new_start_date <= 'end_date' AND $new_end_date >= 'start_date'

 

  New       new start <=    new end >=
Dates      current end    current start
               (1-7)           (1-3)
=========================================
1-1 to 1-2     Yes             No
1-1 to 1-5     Yes             Yes
1-1 to 1-9     Yes             Yes
1-4 to 1-6     Yes             Yes
1-5 to 1-9     Yes             Yes
1-8 to 1-9     No              Yes

 

As you can see the first and last scenarios are the only two where the result would be false. So the query I provided will find any current dates where there is a conflict. Let me know if you see any error in that logic.

[samshel]

You are right ! Sorry about that !

 

The difference was in our approaches. you are finding out DB DATES which ARE conflicting, whereas I was trying to check if INPUT DATES are conflicting. but i agree that ur approach is better