Dividing query results by month and getting monthly totals. I'm Stumped

[sjslovechild]

Below is my code for displaying query results of customers it is ordered by the month. I need to get a total for each month below the month and insert a hr below that.

 

So when the month changes I need to:

 

1. Total the hours for that month

2. Below the total insert a hr

3. below the hr continue to the next month.

 

This seems simple but for some reason I'm getting stumped.

 

<?php
$searchbyclient = $_POST['client'];

$host = 'BLah' ;
$user = 'BLah' ;
$pass = 'BLah' ;
$db = 'BLah' ;
$table = 'worklog' ;
$show_all = "SELECT * FROM $table WHERE clientname = '$searchbyclient'";

mysql_connect ($host,$user,$pass) or die ( mysql_error ());
mysql_select_db ($db)or die ( mysql_error ());
$result = mysql_query ($show_all) or die ( mysql_error ());

echo "<h2>Customer Time Reporting Results</h2>";

echo "<div><a href='reportform.php'>Run another report</a> | <a href='submitjob.php'>Back to Submit Job</a></div>";



echo "<table border='0' cellpadding='2' cellspacing='18' align='center'>";
echo "<tr><th>Date</th><th>Clientname</th><th>Job</th><th>Designer</th><th>Ad Rep</th><th>Time Spent</th></tr>";
$currentdate ='';
while($row = mysql_fetch_array($result)){

	// Print out the contents of each row into a table


if($row['datetime'] != $currentdate)
	{
	echo "<tr><td bgcolor='#FFFFFF'>"; 
	$output = date('F, d, Y', strtotime($row['datetime']));
	echo $output;

	echo "</td><td>"; }
	echo $row['clientname'];
	echo "</td><td>"; 
	echo $row['job'];
	echo "</td><td>"; 
	echo $row['designer'];
	echo "</td><td>"; 
	echo $row['adrep'];
	echo "</td><td>";
	echo $row['time'];
	echo "</td></tr>";
	} 

	echo "<tr><td bgcolor='#FFFFFF' colspan='6' align='right'>";

	//this is the query that adds up the time entered



	$query1 = "SELECT SUM(time) FROM worklog WHERE clientname = '$searchbyclient'";

		$result1 = mysql_query($query1) or die(mysql_error());

		// Print out result
		while($row = mysql_fetch_array($result1)){
		echo "Total = ". $row['SUM(time)'];
		echo "<br />";

		};

	echo "</td></tr>";
	echo "</table>"; 


?>

[GingerRobot]

You need to order your query by your date, then keep track of the month. When it changes, echo out and reset the total. I couldn't see where this was actually needed in your code, but it would look something like this:

 

<?php

$sql = "SELECT *,DATE_FORMAT(yourdatefield,'%c') AS month FROM yourtable ORDER BY yourdatefield";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
$prevmonth = mysql_result($result,0,'month');
$total = 0;

while($row=mysql_fetch_assoc($result)){
if($prevmonth != $row['month']){
	echo $total.'<hr />';
	$total = 0;
	$prevmonth = $row['month'];
}
echo $row['hours'];
//echo out any other information
$total += $row['hours'];

}

?>

 

I've assumed you have some sort of timestamp stored in your database and have therefore extracted the month from this.

[sjslovechild]

The search form that passes information to this code is a drop down menu of Clients. The query has to be by clients. Mind you dates are passed back from the database. Is there a way to keep the query by client and still track the month. So that when it changes I can insert a <HR> and the total hours work on that client for that month?