to find the first row----- it's urgent

[rashmi_k28]

hi,

 

I have the fields like this

Name| status|timestamp

 

The status can be 0 or 1.

 

I have to find the timestamp difference between the status when it changes from 0 to 1.

 

here I have to find the difference of timestamp 

 

 

master          | 2008-03-24 09:10:02 |      0 |

| cluster      | 2008-03-24 09:20:01 |      0 |

| master| 2008-03-24 09:20:01 |      1 |

| cluster              | 2008-03-24 09:30:01 |      0 |

| master          | 2008-03-24 09:30:01 |      1 |

| cluster              | 2008-03-24 09:40:01 |      0 |

| cluster              | 2008-03-24 09:50:02 |      0 |

| cluster              | 2008-03-24 10:00:01 |      1 |

| cluster              | 2008-03-24 10:10:01 |      0 |

| cluster              | 2008-03-24 10:20:01 |      0 |

| cluster              | 2008-03-24 10:30:01 |      0 |

| cluster              | 2008-03-24 10:40:01 |      1 |

| cluster              | 2008-03-24 10:50:01 |      0 |

| cluster              | 2008-03-24 11:00:01 |      0 |

| cluster              | 2008-03-24 11:10:01 |      0 |

| cluster              | 2008-03-24 11:20:02 |      0 |

| cluster              | 2008-03-24 11:30:02 |      1 |

| cluster              | 2008-03-24 11:40:01 |      0 |

| cluster              | 2008-03-24 11:50:02 |      0 |

| cluster              | 2008-03-24 12:00:01 |      0 |

| master              | 2008-03-24 12:00:01 |      0 |

| cluster              | 2008-03-24 12:10:01 |      0 |

| master              | 2008-03-24 12:10:01 |      0|

| cluster              | 2008-03-24 12:20:01 |      0 |

| master              | 2008-03-24 12:20:01 |      1 |

 

 

This is the result got from database.

 

 

When I print the difference I get the result for all the rows. I have to get only when status changes. For example the timestamp is at 2008-03-24 12:00:01 is 0 and name is master I should not calculate the difference of 2008-03-24 12:10:01 .

I have to directly calculate the differnce from 2008-03-24 12:00:01 to 2008-03-24 12:20:01 .

 

 

How to avoid the in between rows which has the status as 0.

 

Please help me with this

[ansarka]

(SELECT * FROM `table` where status=1 order by date  desc limit 1) union (

SELECT * FROM `table` where status=0 order by date  desc limit 1)

 

you will get two rows with latest status change from  1 to 0 or 0 to 1

[Barand]

the first bit of code just makes your posted sample usable

 

<?php
$str = "|master           | 2008-03-24 09:10:02 |      0 |
| cluster      | 2008-03-24 09:20:01 |      0 |
| master| 2008-03-24 09:20:01 |      1 |
| cluster              | 2008-03-24 09:30:01 |      0 |
| master           | 2008-03-24 09:30:01 |      1 |
| cluster              | 2008-03-24 09:40:01 |      0 |
| cluster              | 2008-03-24 09:50:02 |      0 |
| cluster              | 2008-03-24 10:00:01 |      1 |
| cluster              | 2008-03-24 10:10:01 |      0 |
| cluster              | 2008-03-24 10:20:01 |      0 |
| cluster              | 2008-03-24 10:30:01 |      0 |
| cluster              | 2008-03-24 10:40:01 |      1 |
| cluster              | 2008-03-24 10:50:01 |      0 |
| cluster              | 2008-03-24 11:00:01 |      0 |
| cluster              | 2008-03-24 11:10:01 |      0 |
| cluster              | 2008-03-24 11:20:02 |      0 |
| cluster              | 2008-03-24 11:30:02 |      1 |
| cluster              | 2008-03-24 11:40:01 |      0 |
| cluster              | 2008-03-24 11:50:02 |      0 |
| cluster              | 2008-03-24 12:00:01 |      0 |
| master               | 2008-03-24 12:00:01 |      0 |
| cluster              | 2008-03-24 12:10:01 |      0 |
| master               | 2008-03-24 12:10:01 |      0|
| cluster              | 2008-03-24 12:20:01 |      0 |
| master               | 2008-03-24 12:20:01 |      1 |";

    /****************************************************
    * clean up the posted data and store in array
    *****************************************************/
$arr = explode("\n",$str);
$data = array();
foreach ($arr as $line)
{
    $line = trim($line, ' |');
    $tmp = explode ('|', $line);
    foreach ($tmp as $k=>$v) $tmp[$k] = trim($v);
    $data[] = $tmp;
}

    /***********************************
    * process the array
    ************************************/
$prevStatus = '';
$storedTime = '';
echo '<pre>';
foreach ($data as $row)
{
    if ($row[0] != 'master') continue;
    if ($row[2] != $prevStatus)
    {
        if ($prevStatus != '')
        {
            $d = strtotime($row[1]) - strtotime($storedTime);
            
            printf ('Old: %d  New: %d   Diff: %d  <br/>', $prevStatus, $row[2], $d);
        }
        $prevStatus = $row[2];
        $storedTime = $row[1];
    }
}
echo '</pre>';
    
?>

RESULTS ----->

Old: 0  New: 1   Diff: 599  
Old: 1  New: 0   Diff: 9600  
Old: 0  New: 1   Diff: 1200

[rashmi_k28]

Hi,

 

Thanks for the reply.

 

$previousStatus = '';
$currentStatus = '';
$previoustime='';
$currentTime='';
//$storedTime = '';

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

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

        }
    $previousStatus = $currentStatus;
}

}

 

Here if status is 0 and status is not changed the name is not printed.

The name and entire row is pushed into array only if the status is changed.How to get the value if status is 0 through out the value in database.

 

 

The previous timestamp is the fetched as the first timestamp of curent date in the table. How to tak the previous timestamp