David Otton wrote:
2009/12/3 Merlin Morgenstern <merlin_x@xxxxxxxxxxx>:
I am searching for a way to identify the amount of simultanious date ranges.
Example:
array start=('1.12', '5.12', '9.12');
array end =('8.12', '12.12', '16.12');
Looks like this in a table:
start end
1.12 8.12
5.12 12.12
9.12 16.12
Obviously the first and last daterange do not overlap. So the amount of
overlaping bookings is 2. But how to identify this with PHP?!
Store the start and end times of each event in an SQL table.
SELECT COUNT(*) FROM `event` WHERE `start` <= NOW() AND `end` >= NOW()
gets you the number of events that are happening right now.
That is what I thought first, too! But this does not work correct as
there might be a booking starting for example tomorrow. There needs to
be free place for the entire booking period.
I am a bit further now, but still stuck.
So far I could pull out all dates from the database that are within the
range:
SELECT * FROM `datetest` WHERE '2009-12-06' between start and end OR
'2009-12-13' between start and end
Now the trick would be to find overlaps. Here is an example:
I have a table with following booking info:
start end
2009-12-01 2009-12-08
2009-12-05 2009-12-12
2009-12-09 2009-12-16
I want to find out the first possible booking range for 14 day period
begining from 2009-12-06 at the earliest where a max of 3 bookings are
present. The result schould be: 2009-12-06
First I am pulling out all dates between the desired range:
SELECT *
FROM `datetest`
WHERE '2009-12-06'
BETWEEN START AND END OR '2009-12-13'
BETWEEN START AND END
LIMIT 0 , 30
Now the tricky parts starts where I do not know how to find out that the
first daterange in the table and the last daterange do not overlap.
Any ideas?
