如何在php中找到两个连续行中两个字段的区别？

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

我有一个以下格式的机器的mysql日志表。

     ---------------------------------------------------------------------------------------------------
    | 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.

现在我想在php代码中找到每个连续Up和Down Time的区别。

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

我还想将shift2添加到同一个查询中,以显示第1和第2轮的machine1日志。

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

因为我是php的新手,所以无法解决这个问题。

Can any help me out.

可以帮助我。

2 个解决方案

#1

To get shift2 aswell I'd do:

为了获得shift2,我也会这样做:

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.

对于具有空格或特殊字符的列,您需要在它们周围添加“。说实话,对所有列都这样做更安全。某些列(例如Add)会导致查询失败,因为add是sql中的保留关键字。

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

如果你可以保证up将始终跟进,那么你可以遍历你的结果: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.

显然,如果你不能保证在向下之后总会出现向上,或者你想要将shift1和shift2的时间差异分开,那么你将不得不在while循环中添加额外的检查。

---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.

现在你有一个包含两个数组的数组:event_types和timestamps。如果你想要的话,你也可以将它作为两个不同的数组来实现。 $ EVENT_TYPE [] = $行[ 'EVENT_TYPE'];并相应地改变其余部分。

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

您现在可以执行for循环来迭代这些结果并执行所需的检查。

$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.

请记住在for循环之前计算计数,将其置于其中一个条件意味着每次都会计算它,因此具有性能成本。

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.

此外,如果您想跳过mysql结果的第一个结果,只需在while循环之前调用$ row = mysql_fetch_assoc($ data)。

#2

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

您也可以通过加入表格完全在MySQL中完成此操作。像这样(未经测试的)代码应该工作:

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.

有一些极端情况,但这应该让你开始。

#1