date problem

[pouncer]

say in my table (for hotel bookings) i have this:

 

start data end_date (fields)

10-nov-07 15-nov-07

19-nov-07 25-nov-07

 

how do i return the days where no bookings are taking place? i.e:

 

16,17,18 and after 25th november

 

i just want to somehow get the available days

 

[MadTechie]

Build a list of all days and remove the found ones

[pouncer]

yep obviously.

 

but im looking for code help

 

thanks in advance guys

[MadTechie]

okay

<?php
//Build a list of all days and remove the found ones
?>

 

what code do you have so far ?

[rarebit]

start data end_date, are those three separate fields or do you mean start_date end_date?

 

Anyway build an array of all days in month, then build another of days with bookings from db, then unique 'em!

[obsidian]

For starters, I'd recommend you change the data type in which you store your dates to a DATE type. This will allow you to very easily query against it. DATE types are stored as YYYY-MM-DD and can be used in MySQL calculations as well as easily parsed into PHP using functions such as strtotime(). So, assuming you have a DATE type on your database, something like this would work well. This is coming from the perspective of a calendar script, so you could modify it however you like.

<?php
$year = 2007;
$month = 11; // November or date('n') for current month
$days_in_month = date('t', mktime(0,0,0,$month,1,$year));
$available_days = array();
for ($i = 1; $i <= $days_in_month; $i++) // loop through all the days of the month
{
  $ts = mktime(0,0,0,$month,$i,$year); // timestamp for current day in loop
  $date = date('Y-m-d', $ts); // queryable format
  $sql = mysql_query("SELECT * FROM my_table WHERE '$date' BETWEEN start_date AND end_date");
  if (mysql_num_rows($sql) == 0) // no results, so add it to availabilities
  {
    $available_days[] = $i;
  }
}

echo "Days available: " . implode(', ', $available_days);
?>

 

Hope this helps.