complex count between dates

[jenny kaur]

i want to know how i would show the number of booked rooms each day over a given period.

$datein="2005-02-05";
$dateout="2005-02-09";

i tried array_count_values but didnt work.

MY Database table "room":

+---+--------------+---------------+
+ id + ...bookdate...+ ...enddate ... +
+---+--------------+---------------+
+ 1 + 2005-02-05 ..+ 2005-02-06 ...+
+---+--------------+---------------+
+ 2 + 2005-02-05 ..+ 2005-02-07 ...+
+---+--------------+---------------+
+ 3 + 2005-02-05 ..+ 2005-02-08 ...+
+---+--------------+---------------+

$query = "select * FROM room WHERE bookdate < '$dateout' AND enddate > '$datein'"; 

the output i woud like would be like below:

-- 2005-02-05 -- 2005-02-06 -- 2005-02-07 -- 2005-02-08 ---
---- 3 Booked ---- 2 Booked ---- 1 Booked ----- 0 Booked ----

please help!

[thr]

"SELECT count(id) as amount, * FROM room WHERE bookdate < '$dateout' AND enddate > '$datein'"; 

[jenny kaur]

thr,

that would give me all the bookings between those dates but i want number of bookings each day!

output like this:

-- 2005-02-05 -- 2005-02-06 -- 2005-02-07 -- 2005-02-08 ---
---- 3 Booked ---- 2 Booked ---- 1 Booked ----- 0 Booked ----

please help!

[swdev]

Weloom to the SitePoint forumns

This would be better off in the Database forumn or MySQL forum (if you are using MySQL)

[jenny kaur]

swdev. thanks for warm welcome!
this is very much a php question i think as a mysql query will be too big...

what i need is to do a search with mysql and for php to split the dates between dates to get days and how many bookings per day.

anybody?

[swdev]

Ok - here goes some code

   // your start and end dates
   $datein = "2005-02-05";
   $dateout = "2005-02-09";
   
   // start of query to get count of items
   // used here to save creating it every time in the loop
   $select = 'SELECT'
           . ' COUNT(*)'
           . ' FROM'
           . ' room'
           . ' WHERE'
           ;
   
   // convert all dates to UNIX timestamps
   $start_date = strtotime($datein);
   $end_date = strtotime($dateout);
   $current_date = $start_date;
   // array for holding totals for each day
   // the index into the array is the data in yy-mm-dd format
   // this can be changed to anything you want
   $totals = array();
   
   // loop for each day in the seleted range
   while ($current_date < $end_date)
   {
     // convert date for MySQL DATE format
     $tmp = strftime('%Y-%m-%d', $current_date);
     // build where clause
     $where = ' (bookdate <= ' . $tmp . ')'
            . ' AND'
            . ' (' . $tmp . ' < enddate) '
            ;
   
     // full sql statement
     $sql = $select . $where;
   /* ***** START DIAGNOSTIC **** */
   echo $sql . '<br />';
   /* ***** END DIAGNOSTIC **** */
   
     // execute sql statement
     $result = mysql_query($sql) or die ('failed to execute ' . $sql . ' due to ' . mysql_error());
     // get single value from result set and store in totals array
     // this is where you can change the index of the array
     $totals[$tmp] = mysql_fetch_result($result, 0);
   
     // calculate next day
     $current_date = strtotime('+1 day', $current_date);
   }
   
   
   /* ***** START DIAGNOSTIC **** */
   // display totals
   echo '<pre>';
   print_r($totals);
   echo '</pre>';
   /* ***** END DIAGNOSTIC **** */ 

Hopefully the comments will be enough for you to modify as required.

Try it and let us know if it works

[toggg]

Hi check these posts, MsHyde got some verry similar to build.
http://www.sitepoint.com/forums/showthread.php?t=227613
Bye.

[Super Phil]

How many were booked on a given date:

 id   	  book   	  END   	  num_booked_on
1 	2005-02-05 	2005-02-06 	3 booked
4 	2005-02-06 	2005-02-07 	1 booked

SELECT id, book, 
END , concat(count(  *  ), ' booked') num_booked_on
FROM sitepoint
GROUP  BY book

CREATE TABLE `sitepoint` (
  `id` int(11) NOT NULL auto_increment,
  `book` date NOT NULL default '0000-00-00',
  `end` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Dumping data for table `sitepoint`
#

INSERT INTO `sitepoint` VALUES (1, '2005-02-05', '2005-02-06');
INSERT INTO `sitepoint` VALUES (2, '2005-02-05', '2005-02-07');
INSERT INTO `sitepoint` VALUES (3, '2005-02-05', '2005-02-08');
INSERT INTO `sitepoint` VALUES (4, '2005-02-06', '2005-02-07');

If you broke your dates up into multiple integers, maybe we could do this:
http://dev.mysql.com/doc/mysql/en/calculating-days.html

[r937]

if you are interested in a solution where mysql provides the answer in one single query and you don't have to do all that complicated script calculation and (gasp) querying inside a loop...

... let me know

it's a bit tricky, because you have to "generate" each date between the beginning of the date range and the end

but it will allow you to just dump the results straight to print (like you can if you run a query like SELECT * FROM mytable)

i figure, the less code in the application script, the better

[jenny kaur]

swdev,

thanks for the code...it seems to be checking for bookings in and out for the same day which doesnt produce any results... i get:

SELECT COUNT(*) FROM room WHERE (bookdate <= 2005-02-05) AND (2005-02-05 < enddate)
SELECT COUNT(*) FROM room WHERE (bookdate <= 2005-02-06) AND (2005-02-06 < enddate)
SELECT COUNT(*) FROM room WHERE (bookdate <= 2005-02-07) AND (2005-02-07 < enddate)
SELECT COUNT(*) FROM room WHERE (bookdate <= 2005-02-08) AND (2005-02-08 < enddate)

Array
(
[2005-02-05] => Array
(
)

[2005-02-06] => Array
(
)

[2005-02-07] => Array
(
)

[2005-02-08] => Array
(
)

)

hey so is this code making mysql execute 4 queries???

toggg, thanks for link, seems interesting, i will give it a read. cheers.

r937, yeah show me the mysql way, it would be great to have a look! cheers!

ok swdev, where am i going wrong with the code you kindly provided?

[r937]

okay, be glad to

what is the largest range of dates you want to check? the answer will determine how many rows you need in the following table:

create table integers (i integer not null primary key); 
insert into integers (i) values 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11), ... ;

let's use your original range of dates, 2005-02-05 through 2005-02-09, as the example range

note this range is only 4 days, so if you wanted a larger range, say 15 days, you gotta make sure you have at least the first 15 integers in your integers table

okay, the way the query works, it uses the integers to generate a date, by adding the integer to the starting date of the range

then it uses a left outer join to match each date in the range to any row whis spans that date, i.e. the generated date is between bookdate and enddate

simple, eh? just run the query and you get results

select date_add('2005-02-05', interval i day) as thedate
     , count(room.id) as bookings
  from integers
left outer
  join room
    on date_add('2005-02-05', interval i day)
       between room.bookdate 
           and room.enddate
 where i 
       between 0 
           and to_days('2005-02-09')
              -to_days('2005-02-05')      
group
    by thedate
order
    by thedate

note that in the above query you would replace '2005-02-05' with a parameter for the starting date, and replace '2005-02-09' with a parameter for the ending date

let me know if you have any questions

[swdev]

r937 My priginal post said that I though there was a purley SQL way of doing it, but my SQL skills aren't that good

jenny kaur

Here is the corrected code

  // your start and end dates
  $datein = "2005-02-05";
  $dateout = "2005-02-09";
  
  // start of query to get count of items
  // used here to save creating it every time in the loop
  $select = 'SELECT'
         . ' COUNT(*)'
         . ' FROM'
         . ' room'
         . ' WHERE'
         ;
     
  // convert all dates to UNIX timestamps
  $start_date = strtotime($datein);
  $end_date = strtotime($dateout);
  $current_date = $start_date;
  // array for holding totals for each day
  // the index into the array is the data in yy-mm-dd format
  // this can be changed to anything you want
  $totals = array();
     
  // loop for each day in the seleted range
  while ($current_date < $end_date)
  {
    // convert date for MySQL DATE format
    $tmp = strftime('%Y-%m-%d', $current_date);
    // build where clause
    $where = ' (bookdate <= \'' . $tmp . '\')'
           . ' AND'
           . ' (\'' . $tmp . '\' < enddate) '
           ;
  
    // full sql statement
    $sql = $select . $where;
  
  /* ***** START DIAGNOSTIC **** */
  echo $sql . '<br />';
  /* ***** END DIAGNOSTIC **** */
     
    // execute sql statement
    $result = mysql_query($sql) or die ('failed to execute ' . $sql . ' due to ' . mysql_error());
    // get single value from result set and store in totals array
    // this is where you can change the index of the array
    $totals[$tmp] = mysql_result($result, 0);
  
    // calculate next day
    $current_date = strtotime('+1 day', $current_date);
  }
     
     
  /* ***** START DIAGNOSTIC **** */
  // display totals
  echo '<pre>';
  print_r($totals);
  echo '</pre>';
  /* ***** END DIAGNOSTIC **** */ 

hope this helps

------
EDIT

Must learn to post faster

Will read and learn from r937 SQL

[jenny kaur]

r937, why thanks, but how do i get to put my results on screen?

ie.

Room 1: 3 rooms booked

etc...

[jenny kaur]

swdev, wow it works like a charm...

okay now can i use the booked numbers and store them as a variable of some sort?

case 1:
echo one booked room
break;
case 2:
echo two booked room
break;

cheers!

[swdev]

Yes you can. The number of room booked on a give date is held in the $totals array


for example, the number of rooms booked on 2005-02-06 is $totals['2005-2-06'].

Note - I would use r937's all SQL solution. It is far more efficient than mine is.

If you want, I can put r937 SQL statement into PHP code for you, if r937 hasn't already done it.

[jenny kaur]

swdev,

you are amazing, yes please put mr.r937's code in the php code...

also as i am a learner of PHP, can you tell me with your PHP code, how i would output all the booked rooms in a single line...i.e.

2, 1, 0, 0

cheers!

[swdev]

Ok -coming right up
Just let me grab a coffee (it's the middel of the night here )

Not quite sure I understand you last comment - do you want a single string with the just the total number of rooms booked in it, listed by day?

[jenny kaur]

swdev,

we are both in UK...so middle of night where i am too! so will u grab me a coffee too!

okay i want something simular to this:

while( $result = mysql_fetch_assoc($query)){
$id=$result['id'];

echo"$id<BR>";

}

//assume 5 results will produce:
1
2
3
4
5 

[swdev]

Ah - so we are both nite owls

Here's the code,

 
 // connect to you database server here
 
 // select your database here
 
 
  // your start and end dates
// dats muts be in the yyyy-mm-dd format
 $datein = "2005-02-05";
  $dateout = "2005-02-09";
  
  // array for holding totals for each day
  // the index into the array is the data in yyyy-mm-dd format
  // this can be changed to anything you want
  $totals = array();
  
  $sql = 'SELECT'
       . ' DATE_ADD(\'' . $datein . '\', interval i day) as thedate'
       . ', COUNT(room.id) as bookings'
       . ' FROM'
       . ' integers'
       . ' LEFT OUTER'
       . ' JOIN room'
       . ' ON DATE_ADD(\'' . $datein . '\', interval i day)'
       . ' BETWEEN room.bookdate AND room.enddate'
       . ' WHERE'
       . ' i'
       . ' BETWEEN 0 AND TO_DAYS(\'' . $dateout . '\') - TO_DAYS(\'' . $datein . '\')'
       . ' GROUP'
       . ' BY thedate'
       . ' ORDER'
       . ' BY thedate'
       ;
  /* ***** START DIAGNOSTIC **** */
  // display sql
  echo '<pre>';
  print_r($sql);
  echo '</pre>';
  /* ***** END DIAGNOSTIC **** */ 
  
  $result = mysql_query($sql) or die ('Failed to execute ' . $sql .  ' due to ' . mysql_error());
  while ($row = mysql_fetch_assoc($result))
  {
    $totals[$row['thedate']] = $row['bookings'];
  }
  mysql_free_result($result);
  
  /* ***** START DIAGNOSTIC **** */
  // display totals
  echo '<pre>';
  print_r($totals);
  echo '</pre>';
  /* ***** END DIAGNOSTIC **** */ 
  
  // print bookings for each date
  foreach ($totals as $date => $rooms_booked)
  {
    $str = (1 == $rooms_booked) ? ' room booked' : ' rooms booked';
    echo $date . ' has ' . $rooms_booked . $str . '<br />';
  } 

This assumes that you have build the integers table as suggested by r937.

[jenny kaur]

ok fellow nite owl, i will give that a try but i think i didnt exlplain myself properly about the $var;

the whole idea of the room count is to measure demand and set price accordingly...

i.e
0 rooms booked and price is $20 for instance
1 rooms booked and price is $30
2 and price is $40

so i will add these to the top of script

$rate1=20;
$rate2=30;
$rate3=40; 

now i want to use the booked room count to give me the rate so i want

switch $booked_room_count {
case: 0
$rate=$rate1
break;
case: 2
$rate=$rate2
break;
case: 3
$rate=$rate3
break;
} 

so i can then do

echo"Room 1: 2 booked, rate is $rate3"; 

hope it makes sense!

[r937]

swdev, thanks for writing the php

i don't do php at all, i do coldfusion

i only come into this forum when i see the thread might involve sql

[swdev]

Ok - I think this is closer to what you want

  // your start and end dates
  $datein = "2005-02-05";
  $dateout = "2005-02-09";
  
  // room rates
  //   index is the number of rooms booked
  //   value is the cost of the room
  $rack_rate[0] = 20;            // 0 rooms booked rate = 20
  $rack_rate[1] = 30;            // 1 room booked rate = 30
  $rack_rate[2] = 40;            // 2 rooms booked rate = 40
  
  // array for holding totals for each day
  // the index into the array is the data in yy-mm-dd format
  // this can be changed to anything you want
  $totals = array();
  
  $sql = 'SELECT'
       . ' DATE_ADD(\'' . $datein . '\', interval i day) as thedate'
       . ', COUNT(room.id) as bookings'
       . ' FROM'
       . ' integers'
       . ' LEFT OUTER'
       . ' JOIN room'
       . ' ON DATE_ADD(\'' . $datein . '\', interval i day)'
       . ' BETWEEN room.bookdate AND room.enddate'
       . ' WHERE'
       . ' i'
       . ' BETWEEN 0 AND TO_DAYS(\'' . $dateout . '\') - TO_DAYS(\'' . $datein . '\')'
       . ' GROUP'
       . ' BY thedate'
       . ' ORDER'
       . ' BY thedate'
       ;
  /* ***** START DIAGNOSTIC **** */
  // display sql
  echo '<pre>';
  print_r($sql);
  echo '</pre>';
  $result = mysql_query($sql) or die ('Failed to execute ' . $sql .  ' due to ' . mysql_error());
  while ($row = mysql_fetch_assoc($result))
  {
    $totals[$row['thedate']] = $row['bookings'];
  }
  mysql_free_result($result);
  
  /* ***** END DIAGNOSTIC **** */ 
  
  /* ***** START DIAGNOSTIC **** */
  // display totals
  echo '<pre>';
  print_r($totals);
  echo '</pre>';
  /* ***** END DIAGNOSTIC **** */ 
  
  // print bookings for each date
  foreach ($totals as $date => $rooms_booked)
  {
    $str = (1 == $rooms_booked) ? ' room booked' : ' rooms booked';
 $rate = (true == array_key_exists($rooms_booked, $rack_rate)) ? ' and price is ' . $rack_rate[$rooms_booked] : ' but no rate set ';
    echo $date . ' has ' . $rooms_booked . $str . $rate . '<br />';
  } 

I have added the $rack_rate array at the top of the code, and some extra bits in the foreach loop at the bottom of the code.

r937 - No problem. I suppose someone has to do ColdFusion , but many thanks for the SQl. I never would have thought of that solution.

Off Topic:


coffee was great

[jenny kaur]

r937, we are thankful you do SQL as your SQL worked brilliantly!

okay swdev, i got a minor problem:

i need it to go from 2005-02-05 to 2005-02-08 as 2005-02-09 will be the check out date.

SELECT DATE_ADD('2005-02-05', interval i day) as thedate, COUNT(room_number) as bookings FROM intergers LEFT OUTER JOIN room ON DATE_ADD('2005-02-05', interval i day) BETWEEN room.bookdate AND room.enddate WHERE i BETWEEN 0 AND TO_DAYS('2005-02-09') - TO_DAYS('2005-02-05') GROUP BY thedate ORDER BY thedate
Array
(
[2005-02-06] => 3
[2005-02-07] => 1
[2005-02-08] => 0
[2005-02-09] => 0
)

2005-02-06 has 3 rooms booked
2005-02-07 has 1 room booked
2005-02-08 has 0 rooms booked
2005-02-09 has 0 rooms booked

the results should be like last time
2,1,0,0 respectively

[jenny kaur]

not working...

Fatal error: Call to undefined function: array_key_exists() in \apache\htdocs\motel\test3.php on line 67

offtopic: integers is spelt "intergers"

[swdev]

No array_key_exists ? What version of php are you using?

Ok replace this

 $rate = (true == array_key_exists($rooms_booked, $rack_rate)) ? ' and price is ' . $rack_rate[$rooms_booked] : ' but no rate set '; 

with this

   $rate = (true == in_array($rooms_booked, array_keys($rack_rate))) ? ' and price is ' . $rack_rate[$rooms_booked] : ' but no rate set '; 

To use different dates, change the $datein and $dateout variables at the top of the script, in your example, set $dateout = '2005-02-08';