how to query this??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

how to query this??

Post 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
Last edited by PHPycho on Mon Feb 19, 2007 5:54 am, edited 1 time in total.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post 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...
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Thanks Mr. onion2k you made me very greatful..
It just rocked....
Thanks a lot..
Post Reply