rashmi_k28 Posted March 24, 2008 Share Posted March 24, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97577-to-find-the-first-row-its-urgent/ Share on other sites More sharing options...
ansarka Posted March 24, 2008 Share Posted March 24, 2008 (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 Quote Link to comment https://forums.phpfreaks.com/topic/97577-to-find-the-first-row-its-urgent/#findComment-499292 Share on other sites More sharing options...
Barand Posted March 24, 2008 Share Posted March 24, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97577-to-find-the-first-row-its-urgent/#findComment-499362 Share on other sites More sharing options...
rashmi_k28 Posted March 26, 2008 Author Share Posted March 26, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97577-to-find-the-first-row-its-urgent/#findComment-501033 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.