Store a variable based on loop

sandy1028

Hi,

The fields of a table are
status,timestamp,name.

Status is 0 or 1
Timestamp is increment of 10 minutes.

I have to calculate the timestamp from when status is from 0 to 1.

| 0 | 2008-05-29 14:10:02 |A
| 0 | 2008-05-29 14:20:02 |A
| 1 | 2008-05-29 14:30:02 |A
| 0 | 2008-05-29 14:40:02 |A
| 1 | 2008-05-29 14:50:02 |A

Here at 2008-05-29 14:10:02 status was 0 and at 2008-05-29 14:30:02 status was 1. So I have to calculate the difference between two. And again from 2008-05-29 14:40:02 to 2008-05-29 14:50:02

When I use the below code I get time difference as 20 min and 20 min.

I have used the code as

 $currentStatus = $result['status'];
    $currentTime = $result['timestamp'];
    $previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
    $previoustime = $previoustime == '' ? $currentTime : $previoustime;

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


$previousStatus = $currentStatus;
    $previoustime=$currentTime;

But the calculation is not proper. The previoustime is creating problem. It is taking the currenttime of previous row as the next previoustime.

And if status is 0 through out the day then how to add the condition and calculate time differnce from first row and last row.

Please help me how to solve this problem

kakki

what about...
checking, what lines have a status of "1" and then calculate the differences, like this:
| 1 | 2008-05-29 14:00:02 |A
| 0 | 2008-05-29 14:10:02 |A
| 0 | 2008-05-29 14:20:02 |A
| 1 | 2008-05-29 14:30:02 |A
| 0 | 2008-05-29 14:40:02 |A
| 1 | 2008-05-29 14:50:02 |A
turns to
| 1 | 2008-05-29 14:00:02 |A
| 1 | 2008-05-29 14:30:02 |A
| 1 | 2008-05-29 14:50:02 |A
so between 14:00 and 14:30
and between 14:30 and 14:50

sandy1028

kakki wrote:
what about...
checking, what lines have a status of "1" and then calculate the differences, like this:
| 1 | 2008-05-29 14:00:02 |A
| 0 | 2008-05-29 14:10:02 |A
| 0 | 2008-05-29 14:20:02 |A
| 1 | 2008-05-29 14:30:02 |A
| 0 | 2008-05-29 14:40:02 |A
| 1 | 2008-05-29 14:50:02 |A
turns to
| 1 | 2008-05-29 14:00:02 |A
| 1 | 2008-05-29 14:30:02 |A
| 1 | 2008-05-29 14:50:02 |A
so between 14:00 and 14:30
and between 14:30 and 14:50

If the status doesnot turn to 1 then how to calculate.
First condition: If status changes from 0 to 1 then calculate difference between two
Second conditiion: If status doenot change to 1 calculate difference from first row to last row.

sandy1028

Please update me on this

sandy1028



Name       Start time                      End time
A	2008-06-01 07:00:01	2008-06-01 07:10:01	0:0:10
A	2008-06-01 07:10:01	2008-06-01 09:50:01	0:0:10
A	2008-06-01 09:50:01	2008-06-01 10:00:01	0:0:10
B	2008-06-01 09:50:01	2008-06-01 10:00:01	0:0:10
B	2008-06-01 09:50:01	2008-06-01 10:00:01	0:0:10

Actually in the table it is displayed as the previous time is taken as the next Start time for the next row for the same name.

How to aviod it.

The code used is as below:

while($result = mysql_fetch_array($row))
{
 $currentStatus = $result['status'];
    $currentTime = $result['timestamp'];
    $previousStatus = $previousStatus == '' ? $currentStatus : $previousStatus;
    $previoustime = $previoustime == '' ? $currentTime : $previoustime;
//State change.
    if($previousStatus == $currentStatus)
    {
                $previousStatus=$currentStatus;
                continue;
                }
        $d = strtotime($currentTime) - strtotime($previoustime);
         array_push($records,"$result[2]#$ip[0]#$result[2]#$previoustime#$result[1]#$d#$_");
    $previousStatus = $currentStatus;
        $previoustime=$currentTime;

Actually in the above code the records are pushed into the array even if state change from 1 to 0. But I need only data from 0 to 1.

How can I display it.

How to push records into the array if status is always 0. If status doesnot change to 1 then 1st timestamp and last timestamp difference should be calculated. How to do it