View Full Version : Adding a value in each row to find a total.

07-31-2008, 04:19 PM
As my first project I am writing a small bridging application that retrieves data from an existing timesheet database.
The form displays company, project, start time, end time, notes and I have used J de Silva's code to calculate the time difference between the start time and the end time.
This is then displayed as
sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] )
I now need to take that value from each of the rows and add it together to give me a total time which has me stumped.
I am open to any assistance.
Just for note this is for internal use only to help me produce reports and and a little learning. I do believe in a baptism of fire when developing new skills.

Althoughthe code is spread across a few files my min file is here:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Client Support Record</title>
<link href="./css/style.css" rel="stylesheet" type="text/css"></link>
<br />

//turn on error reporting
error_reporting (E_ALL);
//include login information and other creds.
include ('./include/credentials.php');
include ('./include/timediff.php');
include ('./include/table_names.php');
include ('./include/clientdets.php');
include ('./langsettings.php');
echo '<h1>' . $TEXT['rpt-head'] . '</h1><br /><br />';
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />".DB::errorMessage($connection));
// Assign the query
$table = "SELECT $CLIENT_TABLE .organisation, $PROJECT_TABLE.title, $TIMES_TABLE.start_time, $TIMES_TABLE.end_time, $TIMES_TABLE.log_message, $TASK_TABLE.name FROM $db_database.timesheet_client $CLIENT_TABLE , $db_database.timesheet_project $PROJECT_TABLE, $db_database.timesheet_task $TASK_TABLE, $db_database.$TIMES_TABLE $TIMES_TABLE WHERE ($CLIENT_TABLE .client_id=$PROJECT_TABLE.client_id) AND ($PROJECT_TABLE.proj_id=$TASK_TABLE.proj_id And $PROJECT_TABLE.proj_id=$TIMES_TABLE.proj_id) AND ($TASK_TABLE.task_id=$TIMES_TABLE.task_id) AND ($TIMES_TABLE.start_time>{ts '$starttime'} And $TIMES_TABLE.end_time<{ts '$endtime'}) AND ($CLIENT_TABLE .organisation LIKE '$company') ORDER BY $CLIENT_TABLE .organisation, $TIMES_TABLE.end_time";
//Excecute the query
$result = $connection->query($table);
if (DB::isError($result)){
die("Could not query the database:<br />$table ".DB::errorMessage($result));
//fetch and display the results
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-attrib1'],'</th><th>',$TEXT['rpt-attrib2'],'</th><th>',$TEXT['rpt-attrib3'],'</th><th>',$TEXT['rpt-attrib4'],'</th><th>',$TEXT['rpt-attrib5'],'</th><th>',$TEXT['rpt-attrib6'],'</th></tr>';

while($result_row = $result->fetchRow()){
echo "<tr><td>";
echo $result_row[0] . '</td><td>';
echo $result_row[1] . '</td><td>';
echo $result_row[2] . '</td><td>';
echo $result_row[3] . '</td><td>';
echo stripslashes($result_row[4]) . '</td><td>'; //stripslashes to remove the odd charachters from the MySQL output
//get the difference in time between the start and finish times.
// a START time value from the query
$start = $result_row[2];
// an END time value from the query
$end = $result_row[3];

// what is the time difference between $end and $start?
if( $diff=@get_time_difference($start, $end) )
echo //output the difference in a readable format
sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] ) . '</td></tr>';
else //Provide an error feedback.
echo "Time cannot be calculated";

echo("</table><br />");

//start the total code
$time = 0;
// Left this in just a s a placemarker boy is it vexing me!
$time = sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] );

$total_time = 0;
$total_time = $time;
//New table for the monthly total .
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-total'],'</th><th>';
echo $total_time . '</th></tr>';
echo('</table><br />');
//Close the connection after the results have been displayed.

$timestamp= time();
echo 'Page created,<br />' . date("d/m/y G.i:s" ,$timestamp);

07-31-2008, 04:24 PM
Make a variable OUTSIDE of the while loop and set it to 0. On each iteration of the while loop add $diff['minutes'] AND $diff['hours']*60 (turn the hours into minutes) to that variable - do NOT overwrite the variable.

After you're done in the while loop the variable will contain the total number of minutes. Just divide that by 60 to get hours and mod by 60 to get minutes :)

$var = 0;
while(condition) {
// do stuff
$var += $diff['minutes'];
$var += ($diff['minutes'] * 60);
echo $var . ' Minutes elapsed';

07-31-2008, 06:02 PM
Thanks derzok,

Your prompt was perfect to get my head round what I was actually trying to achieve.