comparing dates

[Cesar Ramos]

hi, i am developing a calculator for a hotel, currently i am stuck in the following:

i have the check in date and the check out date but since the hotel has low season and high season the calculator must say how many days does the date range has in high season i currently have a table with the following

seasonname | seasonbegin | seasonend
carnival         | 2010-02-16   | 2010-02-19

so for example a person has the checkin date in 2010-02-14 an the checkout date in 2010-02-17 so the calculator must give them the following results

total nights 3
high season nights 1
low season nights 2

thanks in advance

Cesar

[C. (http://symcbean.blogspot.com/)]

Its probably easier to do it in SQL:

SELECT s.seasonname, TO_DAYS(TIMEDIFF(GREATEST(booking.begin,
seasonbegin),LEAST(booking.end,season.end)))
FROM seasons s, booking
WHERE seasonbegin<=booking.end
AND seasonend>=booking.start;

Will give you a list of the seasons the booking overlaps with - it
should be evidence how to calculate the remaining days and to
consolidate this.

C.

[Cesar Ramos]

thanks i am going to try it quick question that select i can see it with mysql result right?

--
This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

[Cesar Ramos]

MMM RUN THE QUERY ON PHPMYADMIN AND GOT THE FOLLOWING RESULT

SELECT seasonname, TO_DAYS( TIMEDIFF( GREATEST( '2010-02-10', seasonbegin ) , LEAST( '2010-02-15', seasonend ) ) )
FROM seasons
WHERE seasonbegin <= '2010-02-10'
AND seasonend >= '2010-02-15'

seasonname |  TO_DAYS( TIMEDIFF( GREATEST( '2010-02-10' , seasonbegin ) , LEAST( '2010-02-15' , seasonend ) ) )
carnival         |  NULL

tried with dates out of the range that it should return both null and got the same result mmm

[paolo]

Hello Cesar,

SQL built-in date() function - is a very good tool to use when
working on date and time problem, but you might want to use this great
php date function.

http://learn-php-online.blogspot.com/2009/10/php-date-time-function.html

I hope it will help you :)

Nino Paolo Amarillento
http://best-sql-tutorials.blogspot.com/

[Cesar Ramos]

jejeje thanks for your suggestion Paolo but i already am using that function jejeje i implemented on another booking form that i was using in fact it has been very usefull for other things but in this case it`s no good

well still stuck with comparing those dates tried many sql methods but can`t get it to work any suggestions will be appreciated

thanks

Cesar

[Robert Gonzalez]

Have you tried asking on a SQL group?

--
Robert A. Gonzalez
http://twitter.com/RobertGonzalez

[Cesar Ramos]

i did haven`t got the answer yet but the real problem it`s there is no method in php or sql to do this so i tought that if anyone has an idea of how can this be done should be post.

[Jack Timmons]

On Tue, Jan 19, 2010 at 9:58 AM, Cesar Ramos <cesar...@gmail.com> wrote:
> i did haven`t got the answer yet but the real problem it`s there is no
> method in php or sql to do this so i tought that if anyone has an idea of
> how can this be done should be post.

I have an idea. Doesn't mean you should do it.

How about you strtotime the start and end date of the block period.
Than, strtotime each date in the range. If it falls within the int
value of the start and end of the block period, then it's a high end
date. My next post will consist of entirely all caps simply because
they're the best way to get my point across without seeming like a
douche bag.

Am I the only one who bottom posts anymore?

--
-Jack Timmons
http://www.trotlc.com
Twitter: @codeacula

[Javier Montani]

1) What database are you using?
2) a possible solution would be:
   Select .... from table_name where not( checkout < seasonbegin  and seasonend > checkin )

This will bring all the seasons you need.


Tip: Do a graph of the dates range you need and those you don't. i.e:

                                           seasonbegin                    seasonend
                                                  +-----------------------------------------+         
  checkin    checkout                                                                         checkin    checkout
      +---------------+                                                                                     +------------+                 Not needed

             checkin                         checkout                          checkin                         checkout
               +-----------------------------------+               +---------------+    +------------------------------------+           Needed

2010/1/19 Cesar Ramos <cesar...@gmail.com>

[Cesar Ramos]

MYSQL

mmm going to try that tell you what happens

[paolo]

Hi Cesar,

How about this one -> http://best-sql-tutorials.blogspot.com/2010/01/comparing-dates.html.
Hope that will help. It's a simple sql query.

Nino Paolo Amarillento
http://learn-php-online.blogspot.com/
http://learn-html-online-now.blogspot.com/

[Cesar Ramos]

hi Paolo i think thats just what i need another  thing by making the example and posting it on your blog made me realize that the community of php developers has a lot of valuable developers.

thanks for the support going to try it and tell you guys how it went

Cesar

[paolo]

Yeah! it would be easy for me to post images and syntax
highlighter. :) Just let me know if you still have problems about it.

Nino Paolo Amarillento

[Cesar Ramos]

mmm paolo i just figure something out the high and low its backwards

for example if you count with your fingers as i did :D you will notice that high must be 1 and low should be 2
because

from the 02/14 to the 17 are night of the 14 night of the 15 and night of the 16

that would give us 14 and 15 = 2 on low and 16 on high since the 16 it`s the only night thats on the season

[David Dyess]

DISCLAIMER: First of all, I do not condone using $_POST variables without filtering / validation. I do not condone using php_self for forms, and I do not condone the use of this code in its current state on any live web server. 

I thought this would be a decent exercise for me to work on, so I put some really rough code together.  I like to sit down and just try things sometimes and figure out the best way... this one I had never thought of so here ya go...

<html>

<div>

<form name="input" action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">

<fieldset>

<legend>Season Dates</legend>

Season Start:

<input type="text" name="seasonStart" />

Season End:

<input type="text" name="seasonEnd" />

</fieldset>

<fieldset>

<legend>Guest Dates</legend>

Check In:

<input type="text" name="chkIn" />

Check Out:

<input type="text" name="chkOut" />

</fieldset>

<input type="hidden" name="dateCheck" value="true" />

<input type="submit" value="Submit" />

</form>

</div>

</html>

<?php

if(!empty($_POST['dateCheck'])){

$seasonStart = $_POST['seasonStart'];

$seasonStartTime = strtotime($seasonStart);

$seasonEnd = $_POST['seasonEnd'];

$seasonEndTime = strtotime($seasonEnd);

$chkIn = $_POST['chkIn'];

$chkInTime = strtotime($chkIn);

$chkOut = $_POST['chkOut'];

$chkOutTime = strtotime($chkOut);

echo "<p>Season start: $seasonStart ($seasonStartTime)<br /> Season end: $seasonEnd ($seasonEndTime)</p><p> Check-in: $chkIn ($chkInTime)<br /> Check-out: $chkOut ($chkOutTime)</p>";

//preset our count

$low = 0;

$high = 0;

//we already know the total

$total = ($chkOutTime - $chkInTime) / 60 / 60 / 24;

//let's account for people that had their entire stay in a High season

if(($chkInTime > $seasonStartTime) AND ($chkOutTime < $seasonEndTime)){

$chkInSeason = 'High';

$chkOutSeason = 'High';

$high = $total;

$diffIn = ($chkInTime - $seasonStartTime) / 60 / 60 / 24 . ' days after High Season started';

$diffOut = ($seasonEndTime - $chkOutTime) / 60 / 60 / 24 . ' days before High Season ended';

//if not, let's make sure they were in a High season at all

} elseif(($chkInTime < $seasonStartTime) AND ($chkOutTime < $seasonEndTime)) {

$chkInSeason = 'Low';

$chkOutSeason = 'Low';

$low = $total;

$diffIn = ($seasonStartTime - $chkInTime) / 60 / 60 / 24 . ' days before High Season started';

$diffOut = ($seasonEndTime - $chkOutTime) / 60 / 60 / 24 . ' days before High Season ended';

} else {

//did they just check in before the season began?

if($chkInTime < $seasonStartTime){

$lowstart =  $seasonStartTime - $chkInTime;

$diffIn = $lowstart / 60 / 60 / 24 . ' days before High Season started';

$low = $low + ($lowstart / 60 / 60 / 24);

$chkInSeason = 'Low';

//nope, they checked in after it began

} else {

$highstart =  $chkInTime - $seasonStartTime;

$diffIn = $highstart / 60 / 60 / 24 . ' days after High Season started';

$high = $high + ($highstart / 60 / 60 / 24);

$chkInSeason = 'High';

}

//did they just check out after the season ended?

if($seasonEndTime < $chkOutTime){

$lowend =  $chkOutTime - $seasonEndTime;

$diffOut = $lowend / 60 / 60 / 24 . ' days after High Season ended';

$low = $low + ($lowend / 60 / 60 / 24);

if($chkInSeason === 'High'){

$high = $high + $total - $low;

}

$chkOutSeason = 'Low';

//nope, they checked out before it ended

} else {

$highend = $seasonEndTime - $chkOutTime;

$diffOut = $highend / 60 / 60 / 24 . ' days before High Season ended';

$highend = $chkOutTime - $seasonStartTime;

$high = $high + ($highend / 60 / 60 / 24);

if($chkInSeason === 'Low'){

$low = $low + $total - $high;

}

$chkOutSeason = 'High';

}

}

echo '<p>Checkin to season: '. $chkInSeason . ' (' . $diffIn . ')</p>';

echo '<p>Checkout from season: '. $chkOutSeason . ' (' . $diffOut . ')</p>';

echo '<p>High Days: ' . $high . '</p>';

echo '<p>Low Days: ' . $low . '</p>';

echo '<p>Total Stay: ' . $total . ' days</p>';

}

?>

That is really rough, I know, but it seems to work on the few tests I ran on it.

Hope that is some help at least

~David.D

[Cesar Ramos]

thanks david going to run some test with your code since i don`t have to modify the workflow of the calculator rather than the sql solution

[Cesar Ramos]

EXCELLENT DAVID it`s working the only issue it`s that when you put a date before the season starts end checkouts when the season ends

Season start: 2010-02-16 (1266278400)
Season end: 2010-02-19 (1266537600)

Check-in: 2010-02-12 (1265932800)
Check-out: 2010-02-17 (1266364800)

Checkin to season: Low (4 days before High Season started)

Checkout from season: Low (2 days before High Season ended)

High Days: 0

Low Days: 5

Total Stay: 5 days

[paolo]

Hi Cesar

In that case just add 1 for low column and minus 1 for high column :)

http://best-sql-tutorials.blogspot.com/2010/01/comparing-dates.html.

[David Dyess]

I had that working, but kept finding unpredictable variations if i mixed up the ranges. I guess I took out the wrong part some where.

[Cesar Ramos]

David do you still have the part that you took out?

[Robert Gonzalez]

If you are running a modern version of MySQL, you can use this stored procedure I just wrote:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_season_range`$$

CREATE PROCEDURE `test`.`test_season_range`(IN seasonName VARCHAR(30), IN checkIn date, IN checkOut DATE)

BEGIN

/* Declare our procedure variables */

DECLARE highNights, lowNights, totalNights INT;

DECLARE checkInHigh, checkOutHigh TINYINT;

/* Initialize our total nights */

SELECT DATEDIFF(checkOut, checkIn) INTO totalNights;

/* Initialize our markers to see if the check in and check out dates are inside the high marks */

SET checkOutHigh = (SELECT checkOut BETWEEN seasonbegin AND seasonend FROM test_seasons where seasonname = seasonName);

SET checkInHigh  = (SELECT checkIn  BETWEEN seasonbegin AND seasonend FROM test_seasons where seasonname = seasonName);

/* If both are inside the high mark, the work is easy, totalNights will be all high */

IF checkInHigh = 1 AND checkOutHigh = 1 THEN

SET highNights = totalNights;

SET lowNights = 0;

ELSEIF checkInHigh = 0 AND checkOutHigh = 0 THEN

/* Neither is in high range, so reverse the above */

SET lowNights = totalNights;

SET highNights = 0;

ELSE

/**

* Now we know some dates will be high and others will be low, so find the high dates and 

* subtract those from the total to get the low dates

* 

* The high dates will be either from check in to season end or season begin to check out

*/

IF checkInHigh = 1 THEN

SELECT DATEDIFF(seasonend, checkIn) INTO highNights FROM test_seasons where seasonname = seasonName;

ELSE

SELECT DATEDIFF(checkOut, seasonbegin) INTO highNights FROM test_seasons where seasonname = seasonName;

END IF;

SET lowNights = totalNights - highNights;

END IF;

/* Send it all back in a nice little result set */

SELECT totalNights AS `total`, lowNights AS `low`, highNights AS `high`;

END$$

DELIMITER ;

Feel free to name it what you want. I am hitting a table named `test_seasons` in it. The column names are the names you supplied in your original post.

To use it, simply do:

call test_season_range('carnival', '2010-02-14', '2010-02-17')

 total     low  high  

------  ------  ------

     3       2       1

call test_season_range('carnival', '2010-02-12', '2010-02-19')

 total     low  high  

------  ------  ------

     7       4       3

call test_season_range('carnival', '2010-02-01', '2010-02-06')

 total     low  high  

------  ------  ------

     5       5       0

[Cesar Ramos]

Thanks robert appreciate your help i am currently complementing some thing on davids script after that i will try yours thanks

[sc_mach]

Here is my solution which is a little simpler...

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`get_high_low`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_high_low`(
STARTDATE DATE,
ENDDATE DATE,
SELECTSEASON VARCHAR(20)
)
BEGIN
DECLARE CURRENT_DAY DATE DEFAULT STARTDATE;
DECLARE SEASONSTART DATE;
DECLARE SEASONEND DATE;
DECLARE HIGH INT DEFAULT 0;
DECLARE LOW INT DEFAULT 0;

SELECT `start`, `end` INTO SEASONSTART, SEASONEND FROM dates WHERE
season = SELECTSEASON;

WHILE CURRENT_DAY <= ENDDATE DO
SET HIGH = HIGH + IF((CURRENT_DAY >= SEASONSTART) AND (CURRENT_DAY <
SEASONEND), 1, 0);
SET LOW = LOW + IF((CURRENT_DAY < SEASONSTART) OR (CURRENT_DAY >
SEASONEND), 1, 0);

SET CURRENT_DAY = DATE_ADD(CURRENT_DAY, INTERVAL 1 DAY);
END WHILE;

SELECT DATEDIFF(ENDDATE, STARTDATE) AS 'nights', HIGH AS 'high', LOW
AS 'low';
END$$

DELIMITER ;

[Robert Gonzalez]

Dude, I like that solution. Here is one I was working which is almost an identical copy of yours.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_season_range_while`$$

CREATE PROCEDURE `test`.`test_season_range_while`(IN seasonName VARCHAR(30), IN checkIn date, IN checkOut DATE)

BEGIN

/* High and low night counts */

DECLARE highNights, lowNights INT DEFAULT 0;

/* High night date min, high night date max, check date */

DECLARE highMin, highMax, checkDate DATE;

/* Set the check date to the check in date to start the loop */

SET checkDate = checkIn;

/* Set the high night range limits */

SELECT seasonbegin, seasonend into highMin, highMax FROM test_seasons where seasonname = seasonName;

/* Loop over all the dates in the checked in range and check if they are high */

WHILE checkDate < checkOut DO

/* If the current date in the loop is in the high night range, mark it as high, otherwise mark it low */

IF checkDate < highMax AND checkDate >= highMin THEN

SET highNights = highNights + 1;

ELSE

SET lowNights = lowNights + 1;

END IF;

/* Increment the check date by a day */

SET checkDate = DATE_ADD(checkDate, INTERVAL 1 DAY);

END WHILE;

/* Send it all back in a nice little result set */

SELECT DATEDIFF(checkOut, checkIn) AS `total`, lowNights AS `low`, highNights AS `high`;

END$$

DELIMITER ;

--

This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

--
Robert A. Gonzalez
http://robert-gonzalez.com/
http://twitter.com/RobertGonzalez

[Cesar Ramos]

OK thanks everybody for the help specially to david now here it is, it`s working altought the code needs to be improved for those who don`t know spanish

tarifa alta = high rate
tarifa baja = low rate
si = yes

$seasonStart = $_POST['seasonStart'];
$seasonStartTime = strtotime($seasonStart);
$seasonEnd = $_POST['seasonEnd'];
$seasonEndTime = strtotime($seasonEnd);

$chkIn = " $_POST['checkIN']";
$chkInTime = strtotime($chkIn);
$chkOut = " $_POST['checkOUT']";
$chkOutTime = strtotime($chkOut);
 
echo "<p>Season start: $seasonStart ($seasonStartTime)<br /> Season end: $seasonEnd ($seasonEndTime)</p><p> Check-in: $chkIn ($chkInTime)<br /> Check-out: $chkOut ($chkOutTime)</p>";*/

//preset our count
$low = 0;
$high = 0;
//we already know the total
$total = ($chkOutTime - $chkInTime) / 60 / 60 / 24;

//people that are staying after the season
if (($chkInTime>$seasonEndTime) OR ($chkOutTime<$seasonStartTime)){

$chkInSeason = 'Low';
$chkOutSeason = 'Low';

$low = $total+1;

$diffIn = ($seasonStartTime - $chkInTime) / 60 / 60 / 24 . ' days before High Season started';
$diffOut = ($seasonEndTime - $chkOutTime) / 60 / 60 / 24 . ' days before High Season ended';   
}else{

//let's account for people that had their entire stay in a High season
if(($chkInTime > $seasonStartTime) AND ($chkOutTime < $seasonEndTime)){
$chkInSeason = 'High';
$chkOutSeason = 'High';
$high = $total;
$diffIn = ($chkInTime - $seasonStartTime) / 60 / 60 / 24 . ' days after High Season started';
$diffOut = ($seasonEndTime - $chkOutTime) / 60 / 60 / 24 . ' days before High Season ended';
//if not, let's make sure they were in a High season at all
} elseif(($chkInTime < $seasonStartTime) AND ($chkOutTime < $seasonEndTime)) {
$chkInSeason = 'Low';

$chkOutSeason = 'High';
//$low = $total;
$low = $total - 1;

$diffIn = ($seasonStartTime - $chkInTime) / 60 / 60 / 24 . ' days before High Season started';
$diffOut = ($seasonEndTime - $chkOutTime) / 60 / 60 / 24 . ' days before High Season ended';
} else {
//did they just check in before the season began?
if($chkInTime < $seasonStartTime){
$lowstart =  $seasonStartTime - $chkInTime;
$diffIn = $lowstart / 60 / 60 / 24 . ' days before High Season started';
$low = $low + ($lowstart / 60 / 60 / 24);
$chkInSeason = 'Low';
//nope, they checked in after it began
} else {
$highstart =  $chkInTime - $seasonStartTime;
$diffIn = $highstart / 60 / 60 / 24 . ' days after High Season started';

$high = $high + 1 + ($highstart / 60 / 60 / 24);

$chkInSeason = 'High';
}
//did they just check out after the season ended?
if($seasonEndTime < $chkOutTime){
$lowend =  $chkOutTime - $seasonEndTime;
$diffOut = $lowend / 60 / 60 / 24 . ' days after High Season ended';
$low = $low + ($lowend / 60 / 60 / 24);
if($chkInSeason === 'High'){
$high = $high + $total - $low;
}
$chkOutSeason = 'Low';
//nope, they checked out before it ended
} else {
$highend = $seasonEndTime - $chkOutTime;
$diffOut = $highend / 60 / 60 / 24 . ' days before High Season ended';
$highend = $chkOutTime - $seasonStartTime;
$high = $high + ($highend / 60 / 60 / 24);
if($chkInSeason === 'Low'){
$low = $low + $total - $high;
}
$chkOutSeason = 'High';
}

}
}

if (!empty($high)){
$high=$high-1;
$tarifahigh="Si";
           }
if (!empty($low)){
$low=$low-1;
$tarifalow="Si";
           }          
if (($high > '0') AND ($low=='0')){
$tarifahigh="Si";   
$tarifalow="";

    }
if(($chkInTime < $seasonStartTime) AND ($chkOutTime < $seasonEndTime)) {   

$high = $total - $low;   
if ($high>'0'){
$tarifahigh="Si";
}
}
if (($chkInTime < $seasonStartTime) AND ($chkOutTime > $seasonEndTime)) {
    $chktimes= $chkInTime + $chkOutTime;
    $seasontimes= $seasonStartTime + $seasonEndTime;
    $seachk=$chktimes-$seasontimes;
    $seasontimex= $seasonEndTime - $seasonStartTime;
    if ($seachk==$seasontimex){
    $high= $total - $low;
     if ($high>'0'){
        $tarifahigh="Si";

          }   
         }
    }
echo '<p>Checkin to season: '. $chkInSeason . ' (' . $diffIn . ')</p>';
echo '<p>Checkout from season: '. $chkOutSeason . ' (' . $diffOut . ')</p>';
echo '<p>High Days: ' . $high . '</p>';
echo '<p>Low Days: ' . $low . '</p>';
echo '<p>Total Stay: ' . $total . ' days</p>';

echo "tarifa alta: $tarifahigh";
echo "<br>";
echo "tarifa baja: $tarifalow";

:D  going to do some more test

[Robert Gonzalez]

Seriously? You're going to favor 110 lines of PHP processing, calculations and more calculations over a 25 line query? Just giving an opinion here, but it seems to me that doing what you want to do on the database is going to be way better than having to mash up all that code with your PHP.

That said, glad you got it worked out, though I really don't agree with the way you are doing it.

[Cesar Ramos]

Well you`re right the most optimal way would be to go for the query but the main issue is working things out sometimes i bet it has happend to everyone of us in different situations that you must do things you don`t want to if your boss tells you you are on deadline finish it by yesterday and all of those things and you warned them i need more time to deliver something ready and they say is it working yeah it `s working but no buts give me that so thats pretty much the issue when people needs solutions you have to give them solutions not enlarge the problem

[David Dyess]

I'm glad the code helped. I was going to just post saying strtotime() could be used and decided I wanted to try it out myself. I have to agree with Robert though, I would prefer his solution. I just don't have a lot of experience doing queries like those so I was more confident I could accomplish it on the PHP side. Again, I'm glad you have a solution and I helped, and I understand time crunches as well. 

Thanks Robert and sc_mach, I've learned something new.

~David.D

[Robert Gonzalez]

You know, you can totally take what I did in the stored proc and handle that in PHP. Try this (it works, by the way ;) ):

<?php

/**

 * Assumptions being made:

 *  - $checkIn and $checkOut come from a validated form post

 *  - $highMin and $highMax are values from your database, set from a query

 *  - All dates below are in unix timestamp form

 *  - Neither checkIn nor checkOut fall on a daylight savings date change

 */

/**

 * Let's build our assumptions right up front

 */

$checkIn  = strtotime('2010-02-14'); // Should come from a validated $_POST value

$checkOut = strtotime('2010-02-17'); // Should come from a validated $_POST value

$highMin  = strtotime('2010-02-16'); // Should come from the db as a timestamp - see UNIX_TIMESTAMP

$highMax  = strtotime('2010-02-19'); // Should come from the db as a timestamp - see UNIX_TIMESTAMP

/* These are our counters, for rendering */

$highNights = $lowNights = $totalNights = 0;

/* Set up our check date for looping */

$checkDate = $checkIn;

/* Get the value of a day, in seconds, for incrementing */

$oneDay = 86400; // Hard coded value for 60*60*24 - Why make PHP do it?

/* Loop and set our values */

while ($checkDate < $checkOut) {

  if ($checkDate < $highMax && $checkDate >= $highMin) {

    $highNights += 1;

  } else {

    $lowNights += 1;

  }

  

  $checkDate += $oneDay;

}

/* Get our total nights */

$totalNights = $lowNights + $highNights;

/* Output */

echo '<pre>';

echo "Total nights: $totalNights\n";

echo "High nights:  $highNights\n";

echo "Low nights:   $lowNights\n";

echo '</pre>';

[paolo]

Hello Cesar,

I know you've choose already Davids code, it's a nice code too, but I
just modified my SQL version, I just wanted to share it. Please check
it out. I've entered lots of example dates. ==>
http://best-sql-tutorials.blogspot.com/2010/01/comparing-dates.html

I hope it will be helpful. :D

Cheers,

[Robert Gonzalez]

If I understand the original question correctly, your query is not entirely correct. Dates that do not fall inside the season date range should be "low" dates while dates that fall inside the range would be "high" dates. In your query output for CHECKIN 2010-02-16 and CHECKOUT 2010-02-20 you have 0 low nights and 3 high night even though there are 4 nights total and all four are high.

Test your query on dates that are A) all inside the HIGH range, B) all outside the HIGH range, C) end inside the HIGH range and D) start inside the HIGH range.

--
This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

[Robert Gonzalez]

I just found out mine has a small mistake in it. The conditional inside the while loop should be <= to start, not just <. I just wrote a little function for this so we could test an array of dates. I have no idea why I am having so much fun with this:

<?php

$dates = array(

  array('in' => '2010-02-14', 'out' => '2010-02-17'), // Ends in high

  array('in' => '2010-02-17', 'out' => '2010-02-22'), // Starts in high

  array('in' => '2010-02-16', 'out' => '2010-02-20'), // All high

  array('in' => '2010-02-06', 'out' => '2010-02-15'), // All low

);

echo '<pre>';

foreach ($dates as $date) {

  $vals = getHighLowDateCount($date['in'], $date['out'], '2010-02-16', '2010-02-19');

  echo "Total nights: $vals[total]\n";

  echo "High nights:  $vals[high]\n";

  echo "Low nights:   $vals[low]\n\n";

}

echo '</pre>';

function getHighLowDateCount($checkIn, $checkOut, $highMin, $highMax) {

  /* Make all of the dates into timestamps */

  $checkIn  = strtotime($checkIn);

  $checkOut = strtotime($checkOut);

  $highMin  = strtotime($highMin);

  $highMax  = strtotime($highMax);

  

  /* Initialize the counters */

  $highNights = $lowNights = $totalNights = 0;

  

  /* Set up our check date for looping */

  $checkDate = $checkIn;

  

  /* Get the value of a day, in seconds, for incrementing */

  $oneDay = 86400; // Hard coded value for 60*60*24 - Why make PHP do it?

  

  /* Loop and set our values */

  while ($checkDate < $checkOut) {

    if ($checkDate <= $highMax && $checkDate >= $highMin) {

      $highNights += 1;

    } else {

      $lowNights += 1;

    }

    

    $checkDate += $oneDay;

  }

  

  /* Get our total nights */

  $totalNights = $lowNights + $highNights;

  

  /* Return it as an array */

  return array(

    'total' => $totalNights,

    'high'  => $highNights,

    'low'   => $lowNights,

  );

}

Output of this script is:

Total nights: 3
High nights:  1
Low nights:   2

Total nights: 5
High nights:  3
Low nights:   2

Total nights: 4
High nights:  4
Low nights:   0

Total nights: 9
High nights:  0
Low nights:   9

[paolo]

Hello Robert,

I didn't notice that :) anyway I have fixed it already. :D

Please check it out thanks => http://best-sql-tutorials.blogspot.com/2010/01/comparing-dates.html

[Cesar Ramos]

Well thank you robert fortunatelly, my boss just speaked with the client and they give us some time to make some new modifications so i am going to improve the method using yours :D  now i just have a question about the sql query in the last procedure you made the season dates i can get them from the sql with a simple query like $seasondates=@mysql_result(@mysql_query("select starts, ends from season_dates"),0,0); but yours it uses the query you built but my question altough must of you must know that it`s how the heck do i use that query in php i am not so advanced in using complex querys on sql and php

thanks

[Robert Gonzalez]

Actually, the PHP example I posted would use a simple query like the one you just posted to set the season begin and season end vars.

To use the stored procedure I built you would simple make a string query like usual but call the mysqli_multi_query() function instead of mysql_query() [that is because stored procedures send a status message when they are run which is actually a second result set identifier and mysql_query() doesn't know how to handle that correctly - though that may have been fixed with recent builds of the mysql client for PHP]:

<?php

// ASSUME $conn IS A MYSQLI CONNECTION RESOURCE

$sql = "call stored_procedure_name('param', 'param')";

// Initialize the result array

$results = array();

if (mysqli_multi_query($conn, $sql)) {

  do {

    if (($result = mysqli_use_result($conn)) !== false) {

      while ($row = mysqli_fetch_assoc($result)) {

        $recordSet[] = $row;

      }

      mysqli_free_result($result);

    } else {

      if (mysqli_errno($conn)) {

        // Handle your errors here

      }

    }

  } while (mysqli_next_result($conn));

} else { 

  if (($result = mysqli_query($conn, $sql)) !== false) {

    while ($row = mysqli_fetch_assoc($result)) {

      $recordSet[] = $row;

    }

    mysqli_free_result($result);

  } else {

    if (mysqli_errno($conn)) {

      // Handle errors here

    }

  }

}

Please keep in mind this is done using a mysqli connection, not a mysql connection (meaning using the mysqli* functions rather than the mysql* function. Of course you could always try doing it with a mysql connection. I haven't used those in years though so I am really not sure if they will work on stored procs.

[Cesar Ramos]

question how do i put the stored procedure in phpmyadmin, never worked with them, sorry

[Robert Gonzalez]

Copy the query below, open a PMA query window, paste the query into it then execute it. 

Once it is there, in another query window, execute the following query:

call test_season_range_while('carnival','2010-02-14','2010-02-16')

Make sure your table and column names are correct in the stored proc before you run it.