List of all dates RESERVED in mySQL database (PHP)

My database contains two fields with the Type 'Date':
        `date_arrival`
        `date_departing`

The dates are in this format:
        2009-03-25
        2009-05-17

I want to return a list of all occupied dates.  The problem is that March 25 is occupied and May 17 is occupied but so are all the dates in all of the dates in between!

How can I get an array containing EVERY SINGLE DATE that is occupied?


CREATE TABLE IF NOT EXISTS `rooms_reserve` (
  `user_` bigint(20) NOT NULL,
  `date_arrival` date NOT NULL default '0000-00-00',
  `date_departing` date NOT NULL default '0000-00-00',
  `activation_key` varchar(255) NOT NULL,
  PRIMARY KEY  (`rooms_reserve_`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Open in new window

LVL 16
hankknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is again a perfect target for a so-called calendar table, a table which contains all the possible dates between the "minimum" and "maximum" dates you need for your application.

say the table is named tbl_calendar, with (at least) the column date_value
select c.date_value
  from tbl_calendar c
 where exists ( select null from `rooms_reserve` r
                 where r.date_departing >= c.date_value
                   and r.date_arrival <= c.date_value
              ) 
;

Open in new window

0
hankknightAuthor Commented:
angelIII, you are suggesting that I create a new table?  Is that the best way?

Aren't there any special mySQL commands that can be run on Date fields?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII, you are suggesting that I create a new table?  Is that the best way?
once you are using an application that works with dates, the calendar table is almost a "must have".
it helps to count days/holidays/non-holidays etc etc ...

>Aren't there any special mySQL commands that can be run on Date fields?
mysql date and time functions reference:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hankknightAuthor Commented:
Is this a really bad way to do what I want?  It works, and it takes less database tables.  But is it a resource hog?
<?php
 
$date_range_start = strtotime('2009-04-25');
$date_range_end   = strtotime('2009-05-15');
 
$date_range = $date_range_start;
 
while ($date_range <= $date_range_end) 
{
 echo date('F j, Y', $date_range) . "<br />\n";
 $date_range += 86400;
}
 
?>

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that works for a single reservation...
but I presume your system is for multiple reservations...

also, if you want to get all the dates for a single month, with a "status" if there is a reservation for a given room or not, resp. how many. try to do this without the calendar table:
select c.date_value
  , sum(case when r.date_arrival is null then 0 else 1 end) reservation_count
  from tbl_calendar c
  left join `rooms_reserve` r
    on r.date_departing >= c.date_value
   and r.date_arrival <= c.date_value
 where c.date_value >= '2009-06-01'
    and c.date_value < '2009-06-01'
;

Open in new window

0
sjon1966Commented:
I've seen this problem before.
What you need is a full list of dates. To get that, you can either build a calendar table and have it filled with a dates (which takes space, but is fast when you join to it) or build a couple of views that take virtually no space.

I preferred the views solution because it was only needed for a daily report. I did it like this:

1. Create a nrs table holding integers from 0 to 9:
CREATE TABLE nrs (nr int not null primary key);
INSERT INTO nrs VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

2. Create a view vw_nrs to hold numbers from 0 to 10000:
CREATE VIEW `vw_nrs` AS
      select (`i10000`.`nr` * 10000) + (`i1000`.`nr` * 1000) + (`i100`.`nr` * 100) + (`i10`.`nr` * 10) + `i`.`nr` AS `nr`
      from `nrs` `i10000` join `nrs` `i1000` join `nrs` `i100` join `nrs` `i10` join `nrs` `i`
      order by `nr`;

3. Create a view vw_calendar to hold the dates:
CREATE VIEW `vw_calendar` AS
      select (cast(now() as date) + interval (`vw_nrs`.`nr` - 50000) day) AS `Day`
      from `vw_nrs` order by `Day`

If you now do a select like this you will get a full date range:
SELECT `Day` from vw_calendar WHERE `Day` >= '2008-01-01' AND `DAY` <= '2008-01-31' ORDER BY `Day`
CREATE TABLE nrs (nr int not null primary key);
INSERT INTO nrs VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE VIEW `vw_nrs` AS 
	select (`i10000`.`nr` * 10000) + (`i1000`.`nr` * 1000) + (`i100`.`nr` * 100) + (`i10`.`nr` * 10) + `i`.`nr` AS `nr` 
	from `nrs` `i10000` join `nrs` `i1000` join `nrs` `i100` join `nrs` `i10` join `nrs` `i` 
	order by `nr`;
CREATE VIEW `vw_calendar` AS 
	select (cast(now() as date) + interval (`vw_nrs`.`nr` - 50000) day) AS `Day`
	from `vw_nrs` order by `Day`

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.