SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 45
Thread: complex count between dates
-
Jan 28, 2005, 11:34 #1
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
complex count between dates
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 ...+
+---+--------------+---------------+
PHP Code:$query = "select * FROM room WHERE bookdate < '$dateout' AND enddate > '$datein'";
-- 2005-02-05 -- 2005-02-06 -- 2005-02-07 -- 2005-02-08 ---
---- 3 Booked ---- 2 Booked ---- 1 Booked ----- 0 Booked ----
please help!
-
Jan 28, 2005, 11:52 #2PHP Code:
"SELECT count(id) as amount, * FROM room WHERE bookdate < '$dateout' AND enddate > '$datein'";
-
Jan 28, 2005, 11:55 #3
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Jan 28, 2005, 12:30 #4
Weloom to the SitePoint forumns
This would be better off in the Database forumn or MySQL forum (if you are using MySQL)
-
Jan 28, 2005, 13:04 #5
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jan 29, 2005, 06:21 #6
Ok - here goes some code
PHP 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 **** */
Try it and let us know if it worksLast edited by swdev; Jan 29, 2005 at 06:44. Reason: wrong strftime format :blush:
-
Jan 29, 2005, 08:31 #7
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi check these posts, MsHyde got some verry similar to build.
http://www.sitepoint.com/forums/showthread.php?t=227613
Bye.bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Jan 29, 2005, 10:22 #8
- Join Date
- Oct 2004
- Location
- naperville
- Posts
- 189
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How many were booked on a given date:
Code:id book END num_booked_on 1 2005-02-05 2005-02-06 3 booked 4 2005-02-06 2005-02-07 1 booked
Code: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');
http://dev.mysql.com/doc/mysql/en/calculating-days.html
-
Jan 29, 2005, 11:12 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 29, 2005, 18:13 #10
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
(
)
)
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?
-
Jan 29, 2005, 18:47 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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:Code: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), ... ;
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
Code: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
let me know if you have any questions
-
Jan 29, 2005, 19:02 #12
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
PHP 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 **** */
------
EDIT
Must learn to post faster
Will read and learn from r937 SQL
-
Jan 29, 2005, 19:04 #13
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
r937, why thanks, but how do i get to put my results on screen?
ie.
Room 1: 3 rooms booked
etc...
-
Jan 29, 2005, 19:12 #14
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Jan 29, 2005, 19:17 #15
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.
-
Jan 29, 2005, 19:29 #16
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Jan 29, 2005, 19:36 #17
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?
-
Jan 29, 2005, 19:43 #18
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
PHP Code:while( $result = mysql_fetch_assoc($query)){
$id=$result['id'];
echo"$id<BR>";
}
//assume 5 results will produce:
1
2
3
4
5
-
Jan 29, 2005, 19:53 #19
Ah - so we are both nite owls
Here's the code,
PHP 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 />';
}
-
Jan 29, 2005, 20:06 #20
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
PHP Code:$rate1=20;
$rate2=30;
$rate3=40;
PHP Code:switch $booked_room_count {
case: 0
$rate=$rate1
break;
case: 2
$rate=$rate2
break;
case: 3
$rate=$rate3
break;
}
PHP Code:echo"Room 1: 2 booked, rate is $rate3";
-
Jan 29, 2005, 20:18 #21
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 29, 2005, 20:19 #22
Ok - I think this is closer to what you want
PHP Code:// 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 />';
}
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
-
Jan 29, 2005, 20:22 #23
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
2,1,0,0 respectively
-
Jan 29, 2005, 20:29 #24
- Join Date
- Jan 2005
- Location
- London UK, Tokyo, Japan
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
not working...
Fatal error: Call to undefined function: array_key_exists() in \apache\htdocs\motel\test3.php on line 67offtopic: integers is spelt "intergers"
-
Jan 29, 2005, 20:51 #25
No array_key_exists ? What version of php are you using?
Ok replace this
PHP Code:$rate = (true == array_key_exists($rooms_booked, $rack_rate)) ? ' and price is ' . $rack_rate[$rooms_booked] : ' but no rate set ';
PHP Code:$rate = (true == in_array($rooms_booked, array_keys($rack_rate))) ? ' and price is ' . $rack_rate[$rooms_booked] : ' but no rate set ';
Bookmarks