Page 1 of 1
SELECT where a good is available to hire between 2 dates
Posted: Thu Oct 19, 2006 9:20 pm
by mcccy005
Ok; I asked this question a couple months back without any luck so I moved on to other parts of the application; and now I'm back on to it.
What I want to do is have the user select two dates. Eg. 1 January 2007 and 1 March 2007 and search for all cars which have one booking period free during this period.
So the table structure is sort of like this:
Car_ID.....Date_Booked_From......Date_Booked_Until
A booking CAN start on the same day another booking finished (kinda like hotels I suppose...check out at 10am; check-in at 2pm or whatever).
And what I sort of need to do in psudo code is this:
SELECT all cars (car_id) which have at least 2 days between one date_booked_until and the next date_booked_from AND which is between the users first date (1 Jan 2007) and the users final date (1 March 2007)
Posted: Fri Oct 20, 2006 7:44 am
by impulse()
You'd be best converting dates to a unix timestamp using
Stephen,
Posted: Sat Oct 21, 2006 11:52 am
by pedrotuga
your pseudo code is ok, why dont you just translate it to sql?
basicaly you only need to use the > and < operators
select cari_d from car
where
booked_until < $beggining_date
AND booked_until booked from < $ending_date
this actualy means that you have a non normalized database.
create a table for cars, another for bookings then relate them
Posted: Sun Oct 22, 2006 12:24 am
by mcccy005
There is actually a seperate table for Bookings and a seperate one for cars etc etc; I was just asking it in the most simplified format but thanks for the tip anywys!
WIth the query; maybe I wasnt too clear (i wrote the post in a big rush so sorry about that)
I have the following (this isnt necessarily PHP correct style i realise - just so you can understand it, its all good):
$users_first_date=1 January 2006;
$users_end_date=1 February 2006;
The following bookings may be in the database (again; its in simplified form...there is references etc. in the proper database):
car_id....date_booked_from.....date_booked_until
1 1/1/06 10/1/06
1 10/1/06 13/1/06
1 14/1/06 20/1/06
1 25/1/06 1/2/06
2 31/12/05 10/1/06
2 11/1/06 2/2/06
So car number 1 (ie. where car_id=) has two available booking dates in the period. (1 day between the 13th and 14th of Jan and another one from the 20th - 25th Jan). Car number 2 should NOT be selected.
I want to be able to select this car from the database because there is one booking period free (I want to define a booking period as anything more than 2 days between a date_booked_until and the next date_booked_from so that only the date from the 20th - 25th of Jan will be classified as an "available booking".)
So I would need to check for each car_id; what the difference between a date_booked_until and the next date_booked_from is. And if there is a difference of 2 or more; then select this particular car_id and move on to querying the table for the next car_id.
Understand?? SOrry it sounds confusing!!
Posted: Thu Oct 26, 2006 1:25 am
by mcccy005
I've had one idea on how to do this, but I think it would be extremly draining on the server.
I could select every booking for EVERY car between the first and last date the user is searching for; and then put this into a 2D array which is based on each month (assuming the search period is more than one month apart), which has values of true (booked) or false (available), and then subsequently search through the created array for any 2 consecutive keys which are false, and then say that this particular car is available.
Problem with this is that if I get upward of 100 cars, its going to be a LOT of querying and then processing of the data to determine for each boat if it is available.
What do you guys think?? Any ideas on how I could "measure" the complexity (in terms of use of system resources) of this entire process. This way I could perhaps limit the maximum time between a first and last date (that the user is searching for available cars between) as one month; or three months or whatever.
Thanks