Easier way to get array of every date in range

[mb81]

Given 2 inputs of a startdate and an enddate (in standard MySQL date format), does anyone have any better ideas on how to create an array of every date that is between these two dates?

 

Right now, I am other than converting them to integers based on timestamp, then creating an array using range, then looping through and converting them back to mysql style dates.

 

Is there a secret function I am missing or a trick someone has used?

[doni49]

More info--what's this for?  What exactly do you want to do with this? 

[mb81]

I need to cycle through all the dates in a date range to test for availability. This is for an online reservation system (think like a hotel, one reservation per date).

 

 

[obsidian]

I need to cycle through all the dates in a date range to test for availability. This is for an online reservation system (think like a hotel, one reservation per date).

 

Well, there's no special function per se, but you could do something like this:

<?php
$start = '2008-02-15';
$end   = '2008-03-16';

$dates = array();
for ($ts = strtotime($start); $ts <= strtotime($end); $ts += 86400) // Add one day with each iteration
{
  $dates[] = date('Y-m-d', $ts);
}

print_r($dates);
?>

 

Can you give us a little more information as to what you are trying to do? I'm guessing there may be a way to do some of your calculating within MySQL instead of having to do this all in PHP.

[laffin]

if this is a reservation system, then u shud be using SQL.

and u shudn need a hardcoded system like this.

 

[mb81]

I need to cycle through all the dates in a date range to test for availability. This is for an online reservation system (think like a hotel, one reservation per date).

 

Well, there's no special function per se, but you could do something like this:

<?php
$start = '2008-02-15';
$end   = '2008-03-16';

$dates = array();
for ($ts = strtotime($start); $ts <= strtotime($end); $ts += 86400) // Add one day with each iteration
{
  $dates[] = date('Y-m-d', $ts);
}

print_r($dates);
?>

 

Can you give us a little more information as to what you are trying to do? I'm guessing there may be a way to do some of your calculating within MySQL instead of having to do this all in PHP.

 

Obsidian, that's pretty much where I went with it also after a couple of trial and errors, I found that to be the easiest and cleanest.

[mb81]

if this is a reservation system, then u shud be using SQL.

and u shudn need a hardcoded system like this.

 

 

ok den, can you showz me howz to getz a SQL requrd set with all the dates between diz and dat?

 

P.S. I didn't know we spoke lolCat here.

 

 

[laffin]

than u wud want to go into the SQL forum section.

 

if u have a table like (Yes I know this isnt SQL syntax but shud give u a general idea of the table setup.

  id integer

  start_date date

  end_date date

  client  varchar(40)

 

 

as just an example, u can retrieve Reservations that fall between two dates as

$initial_date = '2008-03-15';

$final_date = '2008-03-16';

SELECT * FROM reservations WHERE start_date >= $initial_date AND start_date <= $final_date

 

which wud grab any reservations that begin within that date

however we also want to check the end dates for any overlap

SELECT * FROM reservations WHERE (start_date >= $initial_date AND start_date <= $final_date) OR (end_date >= $initial_date AND end_date <= $initial_date) OR (start_date >= $final_date AND end_date <= $final_date)

 

which wud return any reservation that is within or overlaps on those dates.

 

I believe all the logic is there

 

 

 

 

 

[obsidian]

ok den, can you showz me howz to getz a SQL requrd set with all the dates between diz and dat?

 

I believe that, for the result you are seeking, you will still have to loop over the dates, but your query may be more optimized to do it via gathering the scheduled dates within your range and then simply looping over them to find which are remaining. Try something like this:

<?php
$start = '2008-02-15';
$end   = '2008-03-16';

// remember that BETWEEN is inclusive
$sql = mysql_query("SELECT date_col FROM events_table WHERE date_col BETWEEN '$start' AND '$end'");
$taken = array();
while ($r = mysql_fetch_row($sql))
{
  $taken = $r[0];
}

$available = array();
for ($i = strtotime($start); $i <= strtotime($end); $i += 86400)
{
  $d = date('Y-m-d', $i);
  if (!in_array($d, $taken))
  {
    $avalable[] = $d;
  }
}

echo "Available Dates:<br />\n";
foreach ($available as $a)
{
  echo "$a<br />\n";
}
?>

 

Hope this helps some.

[mb81]

than u wud want to go into the SQL forum section.

 

if u have a table like (Yes I know this isnt SQL syntax but shud give u a general idea of the table setup.

  id integer

  start_date date

  end_date date

  client  varchar(40)

 

as just an example, u can retrieve Reservations that fall between two dates as

$initial_date = '2008-03-15';

$final_date = '2008-03-16';

SELECT * FROM reservations WHERE start_date >= $initial_date AND start_date <= $final_date

 

which wud grab any reservations that begin within that date

however we also want to check the end dates for any overlap

SELECT * FROM reservations WHERE (start_date >= $initial_date AND start_date <= $final_date) OR (end_date >= $initial_date AND end_date <= $initial_date) OR (start_date >= $final_date AND end_date <= $final_date)

 

which wud return any reservation that is within or overlaps on those dates.

 

I believe all the logic is there

 

 

Laffin, all the logic might be there, but that doesn't answer my question. What you are giving me is a MySQL syntax that doesn't work with the way that I have decided to layout my application. There are a couple issues with that, first of all because even though someone might be there for 7 days, they might only be using a particular room or a particular resources for 1 or 2 days, but it needs to be attached to the original reservation, so I created a table that uses their reservation id, the date, and a resource id.