sjslovechild Posted December 5, 2008 Share Posted December 5, 2008 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>"; ?> Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted December 5, 2008 Share Posted December 5, 2008 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. Quote Link to comment Share on other sites More sharing options...
sjslovechild Posted December 10, 2008 Author Share Posted December 10, 2008 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.