How to find difference of two fields from two consecutive rows in php?

Question

I have a mysql log table of machine in the following format.

     ---------------------------------------------------------------------------------------------------
    | Event_Id | Event_Type | Machine_No | Operator  | Time Stamp          | Shift | Reason     | Count |
     ---------------------------------------------------------------------------------------------------
    | 101      | Up         | Machine1   | operator1 | 2012-06-09 01:03:55 | S1    | Start      | 1     |
    | 102      | Up         | Machine2   | operator2 | 2012-06-09 01:03:55 | S1    | Start      | 1     |
    | 103      | Up         | Machine3   | operator3 | 2012-06-09 01:03:55 | S1    | Start      | 1     |
    | 104      | Down       | Machine1   | operator1 | 2012-06-09 02:03:55 | S1    | Break Down | 1     |
    | 101      | Up         | Machine1   | operator1 | 2012-06-09 02:33:55 | S1    | After BD   | 1     |
     ---------------------------------------------------------------------------------------------------

the table goes on like this.

by passing the following query.

$data = mysql_query("SELECT * FROM rpt_machine_log WHERE machine='machine1' AND shift='Shift1'")

i was able to get the following output.

 101    Up      machine1    operator1   2012-06-09 01:03:55 Shift1  Start of The Shift  1
 106    Down    machine1    operator1   2012-06-09 03:15:55 Shift1  Break               1
 109    Up      machine1    operator1   2012-06-09 03:30:55 Shift1  After The Break     1
 112    Down    machine1    operator1   2012-06-09 03:45:55 Shift1  Break Down          1
 115    Up      machine1    operator1   2012-06-09 05:00:55 Shift1  After Break Down    1
 116    Down    machine1    operator1   2012-06-09 05:30:55 Shift1  Break Down          2
 117    Up      machine1    operator1   2012-06-09 05:45:55 Shift1  After Break Down    2
 118    Down    machine1    operator1   2012-06-09 06:00:55 Shift1  End of Shift        1

Now i want to find the difference of each consecutive Up and Down Time in php code.

I also want to add shift2 to same query to display machine1 log for both shift 1 & 2.

since i'm new to php i was not able to solve this.

Can any help me out.

Answer 1

To get shift2 aswell I'd do:

SELECT * 
FROM rpt_machine_log 
WHERE machine='machine1' AND (shift='Shift1' OR shift='Shift2')
ORDER BY shift,`Time Stamp`

With columns that have spaces or special characters you want to add ` around them. To be honest it is safer to just do this for all columns. Some columns, such as Add would cause a query to fail since add is a reserved keyword in sql.

If you can guarantee that up will always follow down then you could loop through your result: while($row=mysql_fetch_assoc($data)){}

After this there are plenty of ways to go to store the data, could try:

if(isset($timestamp)){
$difftime=strtotime($row['Time Stamp'])-strtotime($timestamp);
$hours=floor($difftime/3600);
$difftime-=$hours*3600;
$minutes=floor($difftime/60);
$difftime-=$minutes*60;
$seconds=$difftime;
$diff_array[]=$hours.":".$minutes.":".$seconds;
unset($timestamp);}
else{
$timestamp=$row['Time Stamp'];}

And there you have an array of all the time differences.

Obviously if you can't guarantee that up will always come after down, or you want to split up the time diffs for shift1 and shift2 then you will have to add in extra checks to the while loop.

---EDIT---

if(isset($timestamp)){
$difftime=strtotime($row['Time Stamp'])-strtotime($timestamp);
$diff_array[]=$difftime;
unset($timestamp);}
else{
$timestamp=$row['Time Stamp'];}

foreach ($diff_array as &$value){
$Uptime += $value;}

$hours=floor($Uptime/3600);
$Uptime-=$hours*3600;
$minutes=floor($Uptime/60);
$Uptime-=$minutes*60;
$seconds=$convert;
$Uptime=$hours.":".$minutes.":".$seconds;

---EDIT2---

while($row=mysql_fetch_assoc($data)){
$array['event_type'][]=$row['event_type'];
$array['timestamp'][]=$row['Time Stamp'];}

Now you have an array of two arrays: event_types and timestamps. You could just do this as two distinct arrays too if you want e.g. $event_type[]=$row['event_type']; and change the rest accordingly.

You could now do a for loop to iterate over these results and do the checks you need.

$count=count($array['event_type']);
for($x=0;$x<$count;$x++){
if($row['event_type'][$x]=='Down' && $row['event_type'][$x+1]=='Up'){
}}

Remember to calculate the count before the for loop, putting it in as one of the conditions means it would be calculated every time and therefore has a performance cost.

Also if you want to skip the first result of your mysql results just call $row=mysql_fetch_assoc($data) once before the while loop.

Answer 2

You can also do this entirely in MySQL by joining back to the table. Something like this (untested) code should work:

SELECT *
FROM rpt_machine_log rpt1, rpt_machine_log rpt2
WHERE rpt1.event_type = "Up"
AND rpt1.machine_no = rpt2.machine_no
AND rpt2.`time stamp` > rpt1.`time stamp`
AND rpt2.event_type = "Down"

There are some corner cases, but this should get you started.