Hi,
My DB contains two Tables, "tbl_RoomRates" and "tbl_Seasons" ...
"tbl_Seasons" looks like this:
seasonsid | SeasonNo | SeasonDateStart | SeasonDateEnd
1 | 1 | 2008-08-01 00:00:00 | 2008-11-30 00:00:00
2 | 2 | 2008-12-01 00:00:00 | 2008-12-19 00:00:00
3 | 3 | 2008-12-20 00:00:00 | 2009-01-07 00:00:00
4 | 4 | 2009-01-08 00:00:00 | 2009-04-30 00:00:00
and "tbl_RoomRates" has various Room Rates for each Room Type depending on the period in "tbl_Seasons"
My present PHP code looks like this:
$SQL = "SELECT PeriodNo, PeriodEnd FROM `Periods` WHERE '$YearIn-$MonthIn-$DayIn 00:00:00' BETWEEN PeriodStart AND PeriodEnd";
$res = mysql_query($SQL, $dbcnx) or die (mysql_error());
$row = mysql_fetch_assoc($res);
echo $SQL;
echo "<BR>";
echo $row['PeriodNo'];
echo "<BR>";
echo "<BR>";
$SQL2 = "SELECT PeriodNo, PeriodStart FROM `Periods` WHERE '$YearOut-$MonthOut-$DayOut 00:00:00' BETWEEN PeriodStart AND PeriodEnd";
$res2 = mysql_query($SQL2, $dbcnx) or die (mysql_error());
$row2 = mysql_fetch_assoc($res2);
echo $SQL2;
echo "<BR>";
echo $row2['PeriodNo'];
$firstperiodduration = (strtotime("$row[PeriodEnd]") - strtotime(date("$YearIn-$MonthIn-$DayIn 00:00:00"))) / (60 * 60 * 24);
$secondperiodduration = (strtotime(date("$YearOut-$MonthOut-$DayOut 00:00:00")) - strtotime("$row2[PeriodStart]")) / (60 * 60 * 24);
echo $row[PeriodEnd];
echo "<BR>";
echo $row2[PeriodStart];
echo "<BR>";
echo $firstperiodduration;
echo "<BR>";
echo $secondperiodduration;
$sp = $row2[PeriodStart]; //startperiod
$ep = $row[PeriodEnd]; //endperiod
$i = $sp+1;
while ($i<$ep)
{
$i++;
$bp[] = $i; //between periods into an array
}
echo "$bp[]";
echo "<BR>";
echo $i;
echo "<BR>";
echo $i++;
echo "<BR>";
Here's what happens, a form sends the values for $YearIn, $MonthIn, $DayIn (as the Check-In or Start Date) and $YearOut, $MonthOut, $DayOut (as the Check-Out or End Date), what I need is that the code should find all the Period Numbers/IDs applicable for the entire given period (it can be 1 period or more than 1 Period), and then I also need the code to find out the Number of nights for each discovered Period Number and the Number of Nights in total.
I've so far been able to find the first and the last Period Numbers only and been able to figure our the number of nights associated with the first and the last Period Numbers but I cannot figure out how to find the other periods inbetween the two and the number of nights associated with each one of them.
For example:
If the input is ... Check-In 20/November/2008 and Check-Out 10/January/2009 then that would cover a total of 4 periods and should find the number of nights in each of the 4 periods and number of nights in total.
Can someone please help?
Thank you very much!
