while loop finding each date between range

[viveleroi0]

I have a MySQL database that I want to run a query with a WHERE condition that looks for records matching an exact date between a date range.

For example, my user chooses a beginning to date and an ending date then I want to a loop to loop through each actual date between those.

Then it will run a query using each date.

My problem is that I cannot get the loop to find each date between the range. I can easily increase the date mathematically (but that has tons of problems), or find the number of days between the range,

How can I loop through a range returning each valid date?

[devinemke]

SELECT * FROM table WHERE date BETWEEN $start_date AND $end_date

[viveleroi0]

Thanks - but I need a loop that returns a count for each date between the range,

That just gives me the total count between the range - I need it seperated by all of the dates between that range.

[devinemke]

SELECT COUNT(*) AS count, date FROM table WHERE date BETWEEN $start_date AND $end_date GROUP BY date

[viveleroi0]

But then how will I display the results of that with each date in its own table row?

[devinemke]

$result = mysql_query("SELECT COUNT(*) AS count, date FROM table WHERE date BETWEEN '" . $start_date . "' AND '" . $end_date . "' GROUP BY date");

echo '
<table border=1>
<tr>
<td><b>count</b></td>
<td><b>date</b></td>
</tr>
';

while ($row = mysql_fetch_assoc($result))
{
	echo '
	<tr>
	<td>' . $row['count'] . '</td>
	<td>' . $row['date'] . '</td>
	</tr>
	';
}
echo '</table>';

[viveleroi0]

Thanks a lot for your help - I was able to get that working.

I only have one question left...

the program I am working on lists how many area codes called us on a single day... thus the reason I was looking to display a count for each day.

But the sql above only shows days where there is a count... but if a certain day between my aforementioned range had zero counts, I want to see that.

How can I include days between my range that has zero counts for that area code?

[devinemke]

$result = mysql_query("SELECT COUNT(*) AS count, DATE_FORMAT(date, '%Y-%m-%d') AS date FROM table WHERE date BETWEEN '" . $start_date . "' AND '" . $end_date . "' GROUP BY date");
while ($row = mysql_fetch_assoc($result)) {$array[$row['date']] = $row['count'];}

echo ' 
<table border=1> 
<tr> 
<td><b>count</b></td> 
<td><b>date</b></td> 
</tr> 
'; 

$start_timestamp = strtotime($start_date);
$end_timestamp = strtotime($end_date);

while ($start_timestamp < $end_timestamp)
{ 
   	$date = date('Y-m-d', $start_timestamp);

	if (isset($array[$date])) {$count = $array[$date];}
	else {$count = 0;}

	$start_timestamp += 86400;

	echo '
<tr> 
<td>' . $count . '</td> 
<td>' . $date . '</td> 
</tr> 
';
}
echo '</table>';

[viveleroi0]

Wow your help has been amazing. I truly appreciate you helping me.

One LAST question... is there an easy way to remove weekends?

[devinemke]

Originally posted by viveleroi0
One LAST question... is there an easy way to remove weekends?

$result = mysql_query("SELECT COUNT(*) AS count, DATE_FORMAT(date, '%Y-%m-%d') AS date FROM table WHERE date BETWEEN '" . $start_date . "' AND '" . $end_date . "' GROUP BY date");
while ($row = mysql_fetch_assoc($result)) {$array[$row['date']] = $row['count'];}

echo ' 
<table border=1> 
<tr> 
<td><b>count</b></td> 
<td><b>date</b></td> 
</tr> 
'; 

$start_timestamp = strtotime($start_date);
$end_timestamp = strtotime($end_date);

while ($start_timestamp < $end_timestamp)
{ 
   	$date = date('Y-m-d', $start_timestamp);
		$day_of_week = date('l', $start_timestamp);
		if ($day_of_week != 'Saturday' && $day_of_week != 'Sunday')
		{		
			if (isset($array[$date])) {$count = $array[$date];}
			else {$count = 0;}

		$start_timestamp += 86400;

		echo '
	    <tr> 
	    <td>' . $count . '</td> 
	    <td>' . $date . '</td> 
	    </tr> 
	    ';
	}
}
echo '</table>';

[viveleroi0]

Thanks for the reply, but that code just throws my machine (1.0GZ 512RAM) into chaos. The CPU is straining at 100% and everything is extremely slow to respond. The browser just sits in Loading... forever if I let it.

This did not happen with the code we had before the weekend code was put in.

Any ideas?

[viveleroi0]

I need to mention that the computer problem does not happen if my range does not include any saturdays or sundays.

[viveleroi0]

I figured it out... the $start_timestamp+=86400 string was inside the if tag - thus it allows an eternal loop when the criteria did not meet the IF.

Thanks for your help!