Hi All,

I have a SQL date range query I need help with.

I have a mysql table called 'Booking' that has a number of fields with 2 being of type 'datetime' - 'start_date'
and 'end_date'.

I want to be able to query the two fields to see if a query date range falls within any of the records.

For example, I have 1 record in the booking table with a start date and time of, 01/01/08 00:00:00 and an end time of 25/01/08 00:00:00. I want to
build a query to say, give me a count of all records that have a booking between 12/12/07 00:00:00 and 05/01/08. In this example, I would get a count of 1.

Because I do not know how to do this, I am getting the total number of days of the query dates and then looping round each day and testing that date
against the record dates. If I find a match, I quit the loop and do not process the new booking. This is totally not the right way of doing it and positive there must be an easier way.

I have provided a cut down version of my current code to help outline my current method.

$explodedate = explode("/", $bookingstartdate);
$dayStart = $explodedate[0];
$monthStart = $explodedate[1];
$yearStart = $explodedate[2];
$vb_continue = 1;

$nodays = $bookingenddate; - $bookingstartdate;

for ($i = 0; $i <= $nodays; $i++)
{
$SQL = "SELECT b.id ";
$SQL.= "FROM BOOKING AS b ";
$SQL.= " AND date(b.end_date) = '".date("Y-m-d",mktime(0, 0, 0, $monthStart, $dayStart+$i, $yearStart))."'";
$SQL.= " AND date(b.start_date) = '".date("Y-m-d",mktime(0, 0, 0, $monthStart, $dayStart+$i, $yearStart))."'";
$SQL.= " AND (time(b.end_date) > '".date("H:i:s",mktime($sel_Starthr, $sel_Startmin, 0, $monthStart, $dayStart+$i, $yearStart))."'";
$SQL.= " AND time(b.start_date) < '".date("H:i:s",mktime($sel_Endhr, $sel_Endmin, 0, $monthStart, $dayStart+$i, $yearStart))."')";

$result = mysql_query($SQL);
if ($result)
{
$num = mysql_num_rows($result);

if ($num > 0)
{

$vb_continue = 0;
break;
}
}
}

Thank you for looking guys...

P