iterate

sandy1028

Here is the value from the table which has the field name, timestamp and status.

I have to find the differnence between the 1 and 2nd, 2nd and 3rd rows.... and check the differnence is less than 11 min. When the difference is no less than 11 min I have to calcualte the timestamp.
Suppose the timestamp 2008-05-18 11:30:01 between 2008-05-18 12:10:01 has less than 10 min each. So calculate the difference between strtotime('2008-05-18 12:10:01') and strtotime('2008-05-18 11:30:01').

| A       | 2008-05-18 11:30:01 |      0 |
| A        | 2008-05-18 11:40:01 |      0 |
| A        | 2008-05-18 11:50:01 |      0 |
| A        | 2008-05-18 12:00:01 |      0 |
| A        | 2008-05-18 12:10:01 |      0 |
| A        | 2008-05-18 12:20:01 |      0 |
| A        | 2008-05-18 17:20:01 |      0 |
| A        | 2008-05-18 17:30:01 |      0 |
| A        | 2008-05-18 17:40:02 |      0 |

NogDog

Are you asking how to do this as part of the database query, or purely within PHP after the query has been done (or either way)? If via SQL, what DBMS are you using?

sandy1028

NogDog wrote:
Are you asking how to do this as part of the database query, or purely within PHP after the query has been done (or either way)? If via SQL, what DBMS are you using?

I am asking in php code. If status is 0 from first row to last row I have to calculate the time difference from first row to last and avoid all the rows in between the status 0.

Here at this timestamp 2008-05-18 11:30:01 and 2008-05-18 12:20:01 status is 0 , so I have to calculate between these two timestamps. The difference between these two timestamp is 10 minutes.

Again from timestamp 2008-05-18 17:20:01 to 2008-05-18 17:40:02 I have to calculate the difference.

I am not finding any idea how can I loop and find the difference in php

NogDog

Basically, you want to have a variable that will "remember" the previous row's data as you loop through the rows. Say you are doing a mysql_fetch_assoc() loop:

$previous = array();
while($row = mysql_fetch_assoc($result))
{
   if(!empty($previous))
   {
      $lastTime = strtotime($previous['timestamp']);
      $thisTime = strtotime($row['timestamp');
      $timeDiffSeconds = $thisTime - $lastTime;
      // here you do whatever if/else checks you want based on $timeDiffSeconds...
      // ...then when done update the $previous values:
      $previous = $row;
   }
}

sandy1028

Hi,

I am finding very difficult to store the previous row data.

Actually records in database is like this

Name| Timestamp | Status

A	| 2008-06-08 05:00:01 |	0
A	| 2008-06-08 05:10:01 |	0
A	| 2008-06-08 05:20:01 |	0
A	| 2008-06-08 05:30:01 |	0
A	| 2008-06-08 05:40:01 |	0
A	| 2008-06-08 05:50:01 |	0
A	| 2008-06-08 06:00:02 |	0
A	| 2008-06-08 06:10:01 |	0
A	| 2008-06-08 06:20:01 |	1
A	| 2008-06-08 06:30:01 |	1
A	| 2008-06-08 06:40:01 |	1
A	| 2008-06-08 06:50:01 |	0
A	| 2008-06-08 07:00:01 |	0
A	| 2008-06-08 07:10:01 |	0
A	| 2008-06-08 07:20:01 |	0
A	| 2008-06-08 07:30:01 |	0
A	| 2008-06-08 07:40:01 |	0
A	| 2008-06-08 07:50:01 |	0
A	| 2008-06-08 08:00:01 |	0
A	| 2008-06-08 08:10:01 |	1
A	| 2008-06-08 08:20:01 |	1
A	| 2008-06-08 08:30:01 |	1
A	| 2008-06-08 08:40:01 |	1
A	| 2008-06-08 08:50:02 |	0
A	| 2008-06-08 09:00:02 |	0

Problem is want to find the difference of only status when it is 0 to 1.
Suppose if timestamp is 0 at 2008-06-08 05:00:01 to 2008-06-08 06:10:01 I have to find the difference and again from 2008-06-08 06:50:01 to 2008-06-08 08:00:01 and so on.

Please help me on this.

I used the code


$row=mysql_query("select * from tablename where date(timestamp)=date(now()");
$previousStatus = '';
$currentStatus = '';
$previoustime='';
$currentTime='';

while($result = mysql_fetch_array($row))
{
 $currentStatus = $result['status'];
    $currentTime = $result['timestamp'];
    $previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
    $previoustime = $previoustime == '' ? $currentTime : $previoustime;

if($previousStatus == $currentStatus)
{
            $previousStatus=$currentStatus;
            continue;
            }
    $d = strtotime($currentTime) - strtotime($previoustime);
     array_push($records,"$result[2]#$result[2]#$previoustime##d");
$previousStatus = $currentStatus;
    $previoustime=$currentTime;

The prblem is if status is 0 then it comes ou of the loop and previoustime is not stored properly, the data is pushed into the array when status changes from 1 to 0.
When data is pushed into the array records, the previoustime will be currenttime of previous row.