Change variable based on previous variable

[matthewst]

The code below will query my database for start, pause, resume, and end work timestamps. It does this by querying the database for "start work" when it finds "start work" it stores the timestamp in a variable called "$start_work". It then loops and looks for pause then loops and looks for resume then loops and looks for end. Then is calculates the total amount of time spent working. This works great when there are a pause and resume to acount for, but when the users just work straight through (no pause or resume) the calculation still has the pause and resume variables populated from the previous loop. Does anyone know how to fix this? I need $pause_work and $resume_work to be empty, but only for the next job. I also track job_id's and employee_id's in the same table if that helps. Or maybe something like:

if next loop action6 != pause then $pause = ""
if previouos loop action6 != resume then $resume = ''

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {

if ($action6=="Start Work"){
$start_work = $time_table4;}

if ($action6=="Pause"){
	$pause_work = $time_table4;}

if ($action6=="Resume"){
	$resume_work = $time_table4;}

elseif ($action6=="Finish Work"){
	$end_work = $time_table4;
	$sec = ($end_work - $resume_work) - ($start_work - $pause_work);
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec); 
	echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}
else{
echo "";}
if($row['action_id'] != $last_value){ 
$last_value = $row['action_id'];}
}}

[NogDog]

Could you simply initialize those variables (to 0 or null) before you start the while($row4 = mysql_fetch_assoc($result4)) loop?

[matthewst]

I tried that, here's the problem doing it that way:

loop 1 found start_work at 8:00____$start_work = "8:00"
loop 2 found pause_work at 8:10$pause_work = "8:10"
loop 3 found resume_work at 8:20$resume_work = "8:20"
loop 4 found end_work at 8:30______$end_work = "8:30"

total_work_time = ($pause - $start) - ($end - $resume)

total_work_time = 20

But if we make pause and resume "0" on each loop then

loop 1 found start_work at 8:00_____$start = "8:00"
loop 2 found pause_work at 8:10_$pause = "8:10"
loop 3 found resume_work at 8:20$resume = "8:20" and now $pause = "0"
loop 4 found end_work at 8:30______$end = "8:30" and now $resume = "0"

total_work_time = ($pause - $start) - ($end - $resume)

total_work_time = -30 (or whatever)

Don't forget that sometimes there are no pauses or resumes. When that happens the script still has the pause and resume in memory from previous records.

[Roger_Ramjet]

Well the simplest way to do this is to just count the rows:
2 rows menas no pause
4 rows means there is a pause

Of course, this is not a robust nor foolproof method. But then the robust and foolproof method is to calculate the time as the data is inserted. That is also the time when you would trap errors that the users WILL make on data entry, like forgetting to resume work and going straight to end work after a pause, or forgetting to pause work and trying to resume work, etc.

[matthewst]

That is ideal, but I was just voluntold to take over the site.

[NogDog]

I've got a feeling there's an easier way to do this, but I'm having trouble making sense out of the code you've shown us. (For instance: where do the values of $action6 and $time_table4 come from?)

Perhaps an explanation of the schema of the job_log table would help.

My first thought - without a better understanding of things - would be to loop through the query results, saving the times to an array. Then when that loop is done, subtract the first value of that array from the last. Then if count($array) is 4, subtract from that total the difference of the 2nd and 3rd elements.

[matthewst]

Got it!

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$last_value = "";
$result4=mysql_query($query4);
while($row4 = mysql_fetch_assoc($result4)) {
	if ($action6=="Start Creation" || $action6=="Start Table PTG" || $action6=="Cutting - Begin" || $action6=="Begin 1st Proof" || $action6=="Proof Print - Begin" || $action6=="Start In-House Fix" || $action6=="Final Print - Begin" || $action6=="Start Outside Change"){
	$start_action = $time_table4;
	$start_count++;}

if ($action6=="Pause"){
$pause_time = $time_table4;
$pause_count++;
$start_action1 = $pause_time - $start_action;}

if ($action6=="Resume"){
$resume_time = $time_table4;
$resume_count++;}

if ($action6=="End Creation" || $action6=="Finish Table PTG" || $action6=="Cutting - End" || $action6=="End 1st Proof -OK" || $action6=="End 1st Proof - FIX" || $action6=="End In-House Fix" || $action6=="Final Print - End" || $action6=="End Outside Change"){
	$end_action = $time_table4;
	$end_count++;

	if ($end_count > $start_count){
	$start_action = "0";
	$start_count--;}

	if ($resume_count < $pause_count){
	$sec = ($end_action - $start_action) + $start_action1;
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec);
	$start_count = "0";
	$end_count = "0";
	$pause_count = "0";
	$resume_count = "0";
	$pause_time = "0";
	$resume_time = "0";
	if ($total_time > "05:00:00" || $total_time <= "00:00:00"){echo "";}
	else {echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}}		

	$sec = ($end_action - $resume_time) + ($pause_time - $start_action);
	$hr = floor($sec/60/60);
	$sec = $sec - $hr * 60 * 60; 
	$min = floor($sec/60); 
	$sec = $sec - $min * 60;
	$total_time = sprintf("%02d:%02d:%02d", $hr, $min, $sec);
	$start_count = "0";
	$end_count = "0";
	$pause_count = "0";
	$resume_count = "0";
	$pause_time = "0";
	$resume_time = "0";
	if ($total_time > "05:00:00" || $total_time <= "00:00:00"){echo "";}
	else {echo "<center><table border = '0' cellspaceing = '0' cellpadding = '-1' width = '10%' bgcolor = '#999999'>";
	echo "<tr><td align = 'center' width = '100%'>$total_time</td>";
	echo "</tr></table></center>";}}

else{
echo "";}

[bradgrafelman]

So, did that resolve your issue? If so, don't forget to mark this thread resolved.