Page 1 of 1
how to query this??
Posted: Mon Feb 19, 2007 3:44 am
by PHPycho
Plz help me a bit
suppose i had the follwing table with values
id booking_from booking_to
1 2007-02-08 2007-02-19
2 2008-03-12 2008-04-22
suppose for the month 2007-02 i had to show the days which has been booked in array..How to perform the query...
also perform the query for the month 2008-04
Posted: Mon Feb 19, 2007 5:07 am
by onion2k
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.
Posted: Mon Feb 19, 2007 5:51 am
by PHPycho
I dont know how to thank you!
Thanks a lot Mr onion2k........
i will give a try for this right now....Hope this will Rock...Hope for the best
Thanks againg to all of you esp Mr onion2k.. for viewing n jotting his
valueable logic...
Posted: Mon Feb 19, 2007 10:35 am
by PHPycho
Thanks Mr. onion2k you made me very greatful..
It just rocked....
Thanks a lot..