MySQL has some very useful date functions that help a lot in situations like this...
Code: Select all
select id,
IF(booking_from < DATE("2007-2-01"), 1, DAY(booking_from)) as day_from,
IF(booking_to > LAST_DAY(DATE("2007-2-01")), DAY(LAST_DAY(DATE("2007-2-01"))), DAY(booking_to)) as day_to
from bookings where
(YEAR(booking_from) <= 2007 and MONTH(booking_from) <= 2) and (YEAR(booking_to) >= 2007 and MONTH(booking_to) >= 2)
Then you'll just need to loop through all the results and work out what days have bookings..
Code: Select all
while ($booking = mysql_fetch_object($result)) {
for($day=$booking->day_from; $day<=$booking->day_to; $day++) {
$bookings[$day][] = $booking->id;
}
}
It's a complicated approach, but it works really well.
---------------------------
I don't normally do this, but here's a working script with all those ideas in it that I wrote to make sure it was going to work:
Code: Select all
<?php
$databaseLink = mysql_connect("localhost","user","password");
mysql_select_db("ooer", $databaseLink);
$year = 2007;
$month = 2;
$sql = "select ";
$sql .= "id, ";
$sql .= "IF(booking_from < DATE(\"$year-$month-01\"), 1, DAY(booking_from)) as day_from, ";
$sql .= "IF(booking_to > LAST_DAY(DATE(\"$year-$month-01\")), DAY(LAST_DAY(DATE(\"$year-$month-01\"))), DAY(booking_to)) as day_to ";
$sql .= "from bookings where ";
$sql .= "(YEAR(booking_from) <= $year and MONTH(booking_from) <= 2) and (YEAR(booking_to) >= $year and MONTH(booking_to) >= 2)";
$result = mysql_query($sql,$databaseLink) or die(mysql_error($databaseLink));
while ($booking = mysql_fetch_object($result)) {
for($day=$booking->day_from; $day<=$booking->day_to; $day++) {
$bookings[$day][] = $booking->id;
}
}
ksort($bookings);
echo "<pre>";
print_r($bookings);
echo "</pre>";
?>
Of course, if you decide to use this method you'll have to figure out how best to use $bookings yourself. That will depend what you're doing with it really.