php mysql compare dates and match with user input dates

Question

I am doing a booking page for house renting.

I have a datepicker were my users submits a UNIX datetime: Arrival and Departure date.
On mySQL I have a house with different seasons with "season_start" date (YYYY-mm-dd), "season_end" date & "price per day".

$seasons_select="SELECT id,season_start,season_end,daily_price FROM ".T_ITEM_SEASONS." WHERE id_item=".ID_ITEM." ORDER BY season_start ";
$res_seasons=mysql_query($seasons_select) or die("Error getting states");
$arrival_date = date('d-m-Y', $arrival_date);
$departure_date = date('d-m-Y', $departure_date);
 while ($seasons_row=mysql_fetch_assoc($res_seasons)){
  $start_date = date('d-m-Y', strtotime($seasons_row["season_start"]));
  $end_date = date('d-m-Y', strtotime($seasons_row["season_end"]));
  $current_date = $start_date;
  while($current_date != $end_date){
   $current_date = date("d-m-Y", strtotime("+1 day", strtotime($current_date)));
   $match_date = $arrival_date;
   while($match_date != $departure_date){
    $match_date = date("d-m-Y", strtotime("+1 day", strtotime($match_date)));
    if ($current_date==$match_date){ 
     echo $current_date.' This is one of the days! <br />';
    }       
   }    
  } 
 }

What is the best way to match if a date inside the user arrival/departure period is inside one or severall seasons?

My code kind of works...
I get the answer I want but after it a "Fatal error: Maximum execution time of 30 seconds exceeded in"

Answer 1

What is the best way to match if a date inside the user arrival/departure period days inside one or severall seasons?

Well, yours looks kinda terrible :-)

I would say, let MySQL do the work – select only those records you want in the first place.

To check if a single date is within a “season”, you have to check if it is greater-equal than the season begin and lesser-equal than the season end.

To get all seasons which any day of the arrival/departure period falls into, you have to check four cases:

(| be the season start and end, and A is the select arrival and D the departure date.)

S: -------------------|-----------------|------------------
1. ---------------A--------------------------D-------------
2. ---------------A-----------------D----------------------
3. -----------------------A---------D----------------------
4. -----------------------A------------------D-------------

1. Season falls into selected period completely.

2. Arrival is before season start, departure before end.

3. Arrival is after season start, departure before end.

4. Arrival is after season start, and departure after season end.

(Before and after always meant including, so really before/on and on/after.)

---

Put inside a query, that could look something like this, with 2013-05-04 as arrival and 2013-07-04 as departure date:

SELECT id, season_start, season_end, daily_price FROM table
WHERE id_item = 1234 AND (
   ('2013-05-04' <= season_start AND '2013-07-04' >= season_end) OR
   ('2013-05-04' <= season_start AND '2013-07-04' <= season_end) OR
   ('2013-05-04' >= season_start AND '2013-07-04' <= season_end) OR
   ('2013-05-04' >= season_start AND '2013-07-04' >= season_end)
)
ORDER BY season_start

Since you said you receive the user input as Unix timestamps, you can either format those with PHP’s date() before inserting them into the query, or you could use MySQL’s FROM_UNIXTIME(123456789, '%Y-%M-%D') in those places instead.

And you should create an index (if not already in place) on the columns season_start and season_end (one index each), for better performance.

---

Edit: As is pointed out in this answer Salman referred to in comments, two conditions (and a little more creativity in applying boolean logic that I had) are enough to check, so the WHERE clause can be simplified as

WHERE id_item = 1234 AND
  ('2013-05-04' <= season_end) and ('2013-07-04' >= season_start)

Answer 2

Instead of looping through all days of a season to see whether a given date is within it's bounds, rather try your query as follows.

"SELECT id,season_start,season_end,daily_price FROM ".T_ITEM_SEASONS." WHERE id_item=".ID_ITEM." AND season_start <= $arrival_date AND season_end >= $departure_date ORDER BY season_start ";

where $arrival_date and $departure_date are the values from your datepickers.

This way you won't have to loop through all the days of the season to find matching dates.

A point to note - Consider the desired result in cases where the arrival and departure dates cross over seasons (arriving in one season and leaving in another) The query as above will only return rows where the arrival and departure dates fall within the same season.