Not sure how to do this....

maeltar · 06-11-2010, 06:02 PM

Okey dokey....
Got one that am struggling with a bit, not sure if I should use a subquery (never used before) or if I should run 2 queries and process the data in php from the queries....

What I need to do is grab 2 dates from a form (easy enough), query the table to ensure the dates are not overlapping any of the dates in the table..

What I have so far is :-

Code:

select av_bookedid, av_datestart, av_dateend from av_bookings where '2010-06-08' between av_datestart and av_dateend;

obviously the date I put in there would be a var from the form...

and a second query :-

Code:

select av_bookedid, av_datestart, av_dateend from av_bookings where '2010-06-12' between av_datestart and av_dateend;

Should I, would I and if so, how do I use a subquery, I get the feeling I should be, but am a bit lost...

Sample from table

Code:

+-------------+--------------+------------+
| av_bookedid | av_datestart | av_dateend |
+-------------+--------------+------------+
| 2           | 2010-07-15   | 2010-07-17 |
| 4           | 2010-07-15   | 2010-07-17 |
| 3           | 2010-06-26   | 2010-07-04 |
| 3           | 2010-09-11   | 2010-09-18 |
| 3           | 2010-07-23   | 2010-08-01 |
| 2           | 2010-08-20   | 2010-08-24 |
| 3           | 2010-07-15   | 2010-07-22 |
| 4           | 2010-09-17   | 2010-09-20 |
| 2           | 2010-07-19   | 2010-08-02 |
| 1           | 2010-10-08   | 2010-10-11 |
| 5           | 2010-10-08   | 2010-10-10 |
| 1           | 2010-07-23   | 2010-07-25 |
| 4           | 2010-07-23   | 2010-07-25 |
| 5           | 2010-07-23   | 2010-07-25 |
| 5           | 2010-10-22   | 2010-10-24 |
| 2           | 2010-08-07   | 2010-08-14 |
| 5           | 2010-08-28   | 2010-09-04 |
| 5           | 2010-07-16   | 2010-07-19 |
| 3           | 2010-06-19   | 2010-06-26 |
| 4           | 2010-07-02   | 2010-07-04 |
| 1           | 2010-08-16   | 2010-08-23 |
| 4           | 2010-08-28   | 2010-09-04 |
| 4           | 2010-07-31   | 2010-08-02 |
| 1           | 2010-06-17   | 2010-06-22 |
| 1           | 2010-07-31   | 2010-08-07 |
| 5           | 2010-06-30   | 2010-07-04 |
| 2           | 2010-08-16   | 2010-08-20 |
| 4           | 2010-07-26   | 2010-07-30 |
| 5           | 2010-07-26   | 2010-07-30 |
| 3           | 2010-08-01   | 2010-09-01 |
| 3           | 2010-07-10   | 2010-07-14 |
| 5           | 2010-08-13   | 2010-08-15 |
| 5           | 2010-08-19   | 2010-08-21 |
| 2           | 2010-10-23   | 2010-10-27 |
| 4           | 2010-08-04   | 2010-08-11 |
| 5           | 2010-08-09   | 2010-08-12 |
| 3           | 2010-12-27   | 2011-01-02 |
| 5           | 2010-12-27   | 2011-01-02 |
| 5           | 2010-09-17   | 2010-09-21 |
| 1           | 2010-07-16   | 2010-07-18 |
| 5           | 2010-10-15   | 2010-10-17 |
| 5           | 2010-07-31   | 2010-08-07 |
| 1           | 2010-07-08   | 2010-07-15 |
| 4           | 2010-08-25   | 2010-08-28 |
| 2           | 2010-09-01   | 2010-09-05 |
| 1           | 2010-06-22   | 2010-06-25 |
| 1           | 2010-08-07   | 2010-08-10 |
| 1           | 2010-06-26   | 2010-07-03 |
| 3           | 2010-06-12   | 2010-06-14 |
| 1           | 2010-07-18   | 2010-07-21 |
| 2           | 2010-06-27   | 2010-07-02 |
| 4           | 2010-06-27   | 2010-07-02 |
| 3           | 2010-06-25   | 2010-05-29 |
| 2           | 2010-06-19   | 2010-06-21 |
| 4           | 2010-06-19   | 2010-06-21 |
| 4           | 2010-07-12   | 2010-07-15 |
| 5           | 2010-06-18   | 2010-06-20 |
| 2           | 2010-07-02   | 2010-07-07 |
| 4           | 2010-09-25   | 2010-09-26 |
+-------------+--------------+------------+

maeltar · 06-13-2010, 01:30 PM

Got it sorted out and thought would post the sql in case anyone else comes across this sort of issue...

Code:

$sql = "select av_bookedid as 'accom_unavail', av_datestart , av_dateend from av_bookings where  av_confirmed !=0 
	AND 
	(av_datestart < '$av_reqdatestart' AND av_dateend >= '$av_reqdatestart') 
	AND 
	(av_datestart <= '$av_reqdateend' AND av_dateend >= '$av_reqdateend') 
	OR 
	(av_datestart >= '$av_reqdatestart'  AND av_dateend <= '$av_reqdateend')";