Pick records between dates

[Julian]

Hi guys.

 

I have this table

 

  id_hotel    |    ratesin      |  ratesout      |    rate

      1        | 2007-06-07  | 2007-07-15  |  100.00

      1        | 2007-09-07  | 2007-12-10  |  150.00

      2        | 2007-05-07  | 2007-08-10  |  150.00

      3        | 2007-04-07  | 2007-09-10  |  120.00

 

What I want to do is to make a selection and show only records between desired dates, example:

 

If I post via form: id_hotel = 1 ratesin=2007-07-07 and ratesout=2007-07-10, the result will show only the records in those dates range in this case the result will be:

 

  id_hotel    |    ratesin      |  ratesout      |    rate

      1        | 2007-06-07  | 2007-07-15  |  100.00

 

Maybe is easier than I think, but I know someone know how to achieve this.  Thanks

[pocobueno1388]

Try this:

 

<?php

mysql_query("SELECT * FROM table WHERE ratesin > $ratesin AND ratesout < $ratesout") or die (mysql_error());

?>

 

$ratesin and $ratesout would of course be the post data variables.

[Julian]

Thanks for the help but I tried this and did not work.

 

I think the reason is if I put >= or >= the query checks the within the column dates.  I need to check between those two dates columns.

 

I hope I explained well.  Thanks again.

[obsidian]

The way you're asking it, you simply need to be sure that your submitted start and end dates are between the start and end of the rate chart dates. However, this will cause a problem when the dates submitted overlap two different rates in your table. What you will probably need to do is loop through the date range entered and query the price for each date. This way, you can get a different rate for each day without causing an error:

<?php
$start = "2007-07-10";
$end  = "2007-08-04";
$ts   = strtotime($start);

$rates = array();
for ($i = strtotime($start); $i <= strtotime($end); $i += (60 * 60 * 24)) {
  $date = date('Y-m-d', $i);
  $sql = mysql_query("SELECT rate FROM rate_table WHERE '$date' BETWEEN ratesin AND ratesout");
  if (mysql_num_rows($sql) == 1) {
    // Found a rate for this date
    $rates[] = array($date, mysql_result($sql, 0, 'rate');
  }
}

echo "<pre>\n";
print_r($rates);
echo "</pre>\n";
?>

 

This should give you a multi-dimensional array with the dates and rates of all the individual dates of your stay. You can then simply tally the rates and get the grand total.

 

Hope this helps!