Page 1 of 1

creating an array of dates from start and end date

Posted: Sun Apr 06, 2003 7:06 am
by lloydie-t
I am trying to create an array of dates based on a start and end date from a MySQL record. I have the following code while the query is OK, I can not create an array. Any Ideas?

Code: Select all

$query = "SELECT cal_date_start, cal_date_end
FROM cal_entry
WHERE year(cal_date_start)<='$year' AND month(cal_date_start)<='$month' AND year(cal_date_end)>='$year' AND month(cal_date_end)>='$month'"; 

$result = mysql_query ($query); 

while($row = mysql_fetch_assoc($result))
$format='Y-m-d h:i:s';
$cal_date_start = explode('-',$row[cal_date_start]);
$syear = $date_array[0];
$smonth = $date_array[1];
$sday = $date_array[2];

$cal_date_end = explode('-',$row[cal_date_end]);
$eyear = $date_array[0];
$emonth = $date_array[1];
$eday = $date_array[2];

$cal_date_start = mktime(00,00,00,$smonth,$sday,$syear);
$cal_date_end = mktime(00,00,00,$emonth,$eday,$eyear);

while($cal_date_start < $cal_date_end) {
    $cal_date_start = $cal_date_start + (24 * 3600);
    $dateArray[] = date($format,$cal_date_start);}
//$dateArray[]=$row['cal_date_start'];
$count = count($dateArray);
$min = 1;
if ($count < $min) {
       $dateArray[] = array('0000-00-00');
}

echo build_calendar($month,$year,$dateArray); 

if (isset ($_GET['date'])) { 

$date = $_GET['date']; 

} else { 

$date = date("d F Y"); 

}
it seems the $count is always 0

Posted: Sun Apr 06, 2003 8:11 am
by volka
"Yes, that is the proper direction to travel, Avatar!" (ultima 6 ;) )
I think your reservation system is advancing.
But the condition in your where-clause is wrong; you're searching for a date that is smaller than your start-date and at the same time bigger than your end-date. Since start-date is probably smaller than end-date this condition cannot be fulfilled. You have to switch > and <

Let me show you something else. If you're sure about using mysql you might take full advantage of its features. For the examples you need to connect to your mysql-server directly (e.g. via mysqlc from console)

first an example directly from http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1134
SELECT 'b' BETWEEN 'a' AND 'c'
this should echo 1 because mysql "decides" that the character b is between a and c and therefor returns 1 for this query. try again with 'z' BETWEEN 'a' AND 'c'.
This also works with dates (the next example is not 100% correct because it relies on an implicit type conversion string->date that will not take place imho. But ignore it for now ;) )

Code: Select all

SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-04-06'
this also returns 1 because the 5th of april is between 03/30 and 04/06.
But why calculate the end date by hand if mysql can do that for you?
take a look at http://www.mysql.com/doc/en/Date_and_time_functions.html

Code: Select all

SELECT '2003-03-30' + INTERVAL 6 day
amazing, isn't it :)

now combine it with BETWEEN

Code: Select all

SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day
let's see wether this works correctly for dates that are not within the interval

Code: Select all

SELECT '2003-04-15' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day
I think this is much simpler...

You can use it for where-clauses, too (would be useless for you if not), e.g.

Code: Select all

SELECT * FROM tablename WHERE datefield BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6
this will return all records having a value for datefield that is between 03/30 and 04/6

the examples as php code (just in case you have no mysql-client)

Code: Select all

<html><body>
	<table border="1">
<?php
$dbHost = '???'; // edit
$dbUser = '???'; // edit
$dbPass = '???'; // edit

$dbConn = mysql_connect($dbHost, $dbUser, $dbPass) or die(mysql_error());

$querries = 
	array(
		"SELECT 'b' BETWEEN 'a' AND 'c'",
		"SELECT 'z' BETWEEN 'a' AND 'c'",
		"SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-04-06'",
		"SELECT '2003-03-30' + INTERVAL 6 day",
		"SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day",
		"SELECT '2003-04-15' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day"
	);
	
foreach($querries as $query)
{
	$result = mysql_query($query, $dbConn) or die(mysql_error());
?>	
		<tr>
			<td><?php echo $query; ?></td>
			<td><?php echo array_shift(mysql_fetch_row($result)); ?></td>
		</tr>
<?php
}
?>
	</table>
</body></html>

Posted: Sun Apr 06, 2003 8:46 am
by lloydie-t
from what I can see this would still require me to know the value between two dates and would not not give me an array with the date values in between. Basically what I am trying to do as follows.

$cal_date_start = 2003-04-05
$cal_date_end = 2003-04-07

would equal

$dateArray[0] = 2003-04-05
$dateArray[1] = 2003-04-06
$dateArray[2] = 2003-04-07

Posted: Sun Apr 06, 2003 8:56 am
by volka
arghh, this is definitly not my day/week/month/year/life ;)
I thought you wanted to create that reservation block system, but this is something completely different :oops:
I think you cannot perform this task in mysql (might be wrong) but maybe viewtopic.php?t=7788 will help you (the thread I thought this question belongs to)

Posted: Sun Apr 06, 2003 9:43 am
by lloydie-t
Volka,
I see where you are going, but I still need to check how many days are between the start and end date, and as my results are still returning 0, I still need to get the array to work or at least be able to count the days between. I think what I have done so far is OK, apart from the fact I cannot generate an array.
8O

Posted: Sun Apr 06, 2003 7:05 pm
by volka
I still don't understand wether you want to select records that are already within the database or mysql to create the range of dates.
For the first case: I think it's only the condition that is wrong
$query = "SELECT cal_date_start, cal_date_end
FROM cal_entry
WHERE year(cal_date_start)<='$year' AND month(cal_date_start)<='$month' AND year(cal_date_end)>='$year' AND month(cal_date_end)>='$month'";

Code: Select all

$query = "SELECT cal_date_start, cal_date_end
FROM cal_entry
WHERE year(cal_date_start)>='$year' AND month(cal_date_start)>='$month' AND year(cal_date_end)<='$year' AND month(cal_date_end)<='$month'";
$row[cal_date_start]
you should quote the string literal unless it's a defined value

Code: Select all

$row['cal_date_start']

Posted: Sun Apr 06, 2003 8:08 pm
by lloydie-t
Thanks Volka,
I have been a bit busy, so have not had a chance to look at the post. Anyway I have managed to get it working, probably not elegantly but it does the job.

Code: Select all

$query = "SELECT cal_date_start, cal_date_end
FROM cal_entry
WHERE year(cal_date_start)<='$year' AND month(cal_date_start)<='$month' AND year(cal_date_end)>='$year' AND month(cal_date_end)>='$month'"; 

$result = mysql_query ($query);
if (mysql_num_rows($result) < 1)
{ $dateArray[] = array('0000-00-00');}
else
{
while($row = mysql_fetch_assoc($result)){
$cal_date_start = $row["cal_date_start"];
$cal_date_end = $row["cal_date_end"];
$format='j';
$cal_array_start = explode(" ",$cal_date_start);
$cal_date_start = explode("-",$cal_array_start[0]);
$syear = $cal_date_start[0];
$smonth = $cal_date_start[1];
$sday = $cal_date_start[2];

$cal_array_end = explode(" ",$cal_date_end);
$cal_date_end = explode("-",$cal_array_end[0]);
$eyear = $cal_date_end[0];
$emonth = $cal_date_end[1];
$eday = $cal_date_end[2];

$calstart = mktime(00,00,00,$smonth,$sday,$syear);
$calend = mktime(00,00,00,$emonth,$eday,$eyear);
$dateArray[] = $sday;
while($calstart < $calend) {
$calstart = $calstart + (24 * 3600);
$dateArray[] = date($format,$calstart);
//$dateArray[]=$row['cal_date_start'];
$count = count($dateArray);
$min = 1;}
if ($count < $min) {
       $dateArray[] = array('0000-00-00');
}}
}
If you can think of anyways to tidy it up let me know