Help with date checking in PHP/MySQL

[squish]

Hello

 

I apologize if this has been asked before. I am pretty beginner at PhP/MySQL and have run into a problem that has stumped me.

 

Basically I am working on a reservation system that allows someone to reserve an item for a specified number of days. This information is stored using two date values in SQL, a date_out and a date_in, the first being when they take out the item, the second being when they return it.

 

At this stage, I am building an error checking system so that when someone goes to reserve an item, the system checks to see whether the dates they have entered for date_in and date_out conflict with another reservation. Basically I'm trying to prevent any overlap.

 

I just can't for the life of me figure out how to check a "range" of dates going off of just the date_in and date_out variables of an existing reservation. I've been trying to think of a way for hours and hours and my brain is starting to hurt pretty bad.

 

To try and paint a better picture, I'll give you an example:

 

In the database, there is an existing reservation for an item, that has two date values:

date_out: 2008-08-26

date_in: 2008-08-30

 

A user goes onto the system and tries to reserve the same item for these dates:

date_out: 2008-08-27

date_in: 2008-08-29

 

Obviously, since that item is already used by the first reservation, the new reservation can't be committed.

 

What I want the system to do, is to see that the new reservation's dates conflict with the old reservation's dates, and print out an error.

 

Anyone have some ideas I can try? Just some pseudo code or a general procedure to follow.

 

Thanks in advance...

[Vermillion]

You basically want to prevent people from reserving another item if has already been reserved by someone, right?

[Grayda]

When I need to compare times / dates, I use strtotime(). Basically you enter in a date and it returns it as a UNIX timestamp (a long number representing the number of seconds since midnight, January 1st, 1970). You then do a time() and subtract one from the other. For example:

 

function isCheckedOut($startDate, $endDate) {

// Get our times as a timestamp
$checkOut = strtotime($startDate);
$checkIn = strtotime($endDate);
// Returns the current time as a timestamp
$now = time();

// Now, we do some comparisons. If the time now is later than the checkout date, but earlier than the checkin time, then there's a conflict
if($now >= $checkOut and $now <= $checkIn) { echo "This item is already booked out!"; } else { echo "Your booking was confirmed!"; }

}

 

And to use it:

 

isCheckedOut("2008-08-26", "2008-08-29");

 

The neat thing with strtotime is, you can give it plain English sentences, such as:

 

echo strtotime("Next Thursday"); // Outputs a timestamp that represents next Thursday
echo strtotime("2nd January, 2009");
echo strtotime("5 hours ago");
echo strtotime("Last Thursday next month"); // Outputs a timestamp that represents Next month, calculated from last Thursday

 

(I think the above examples are correct. Correct me if I'm wrong folks )

 

But I'd be careful about using plain English with isCheckedOut, because you could potentially let people set "Next Thursday" as a date, and that day will never come (strtotime, unless told, uses the current time as it's point of reference)

 

Another solution would be to have a second database that stores all the items that are currently on loan. In semi-pseudocode:

 

function checkOut($item) {

 

// When this code is run, $item (whether it's an ID number, item name, whatever) is copied to a table in your database called "onLoan". onLoan holds nothing but some basic information about the item, such as who loaned it, when they loaned it, the due date, the item number etc.

 

}

 

function isCheckedOut($item) {

 

// When this code is run, the code looks in onLoan for $item (eg. SELECT borrower FROM onLoan WHERE itemNum = '$item' ) and if it's found, assume it's on loan

 

}

 

This has the advantage of being easier to keep track of loans when you're browsing the database manually, plus separates your item description from your item loan. However, I think both are acceptable answers

 

Hope this helps

[paulim]

One approach is to match the dates the user wants against the database using the 'between' like

 

select * from <table> where <dateuserwants_ou> between date_out and date_in
or <dateuserwants_in> between date_out and date_in

 

that will return all the rows that have the item taken using the specific dates your new user wants to take the item for.

 

hope that helps...

[PFMaBiSmAd]

You can do this all in a query the way paulim has posted, but you also need to check if either of the existing date_in and date_out are between the requested in and out dates (the requested in date could be before the existing date_in and the requested out date could be after the existing date_out.)

[paulim]

having the request start date as 2008-09-10 and return date as 2008-09-18, i think the bellow would work...

 

please correct me if i'm wrong.

 

 

select title,start_date,end_date from game where 
('2008-09-10' between start_date and end_date or
'2008-09-18' between start_date and end_date) or
(start_date between '2008-09-10' and '2008-09-18')

[PFMaBiSmAd]

Three comparisons will work. It is only necessary for the third comparison to check if one point of the existing in/out is between the requested in/out.

[paulim]

And don't forget to cast your strings as datetime or date, just in case..

[PFMaBiSmAd]

It should be noted that BETWEEN is inclusive, so by just storing and using the DATE in comparisons, a BETWEEN comparison will show that something is not available if it is brought back on the same day someone else wants to reserve it. The item could actually be available if the time it is brought back is before the time someone else wants it. If you use a DATETIME when storing and comparing the data, you would eliminate this problem.