Getting Range of record using a Nested While Loop MySQL/PHP

Question

I have a Table "dtr", which contains 6 columns namely Name, ACNo, Date, Time, State and Exception.

   Name      | ACNo. |    Date   |    Time      |   State    | Exception|
-------------+-------+-----------+--------------+------------+----------+
Johnny Starks| 1220  | 5/13/2013 | 11:45:18 PM  |  Check In  |   OK     |
Johnny Starks| 1220  | 5/14/2013 | 12:46:58 AM  |  Out       |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 12:52:41 AM  |  Out Back  |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 02:12:50 AM  |  Out       |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 02:43:11 AM  |  Out Back  |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 05:46:58 AM  |  Out       |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 06:22:41 AM  |  Out Back  |   Out    |
Johnny Starks| 1220  | 5/14/2013 | 06:55:12 AM  |  Check Out |   OK     |
Johnny Starks| 1220  | 5/14/2013 | 11:47:13 PM  |  Check In  |   OK     |
Johnny Starks| 1220  | 5/15/2013 | 12:36:28 AM  |  Out       |   Out    |
Johnny Starks| 1220  | 5/15/2013 | 12:59:11 AM  |  Out Back  |   Out    |
Johnny Starks| 1220  | 5/15/2013 | 03:12:54 AM  |  Out       |   Out    |
Johnny Starks| 1220  | 5/15/2013 | 03:33:31 AM  |  Out Back  |   Out    |
Johnny Starks| 1220  | 5/15/2013 | 06:55:12 AM  |  Check Out |   OK     |

So, What i want to get as a result using the codes below is... or the sequence i want the loop to do..

in the 1st loop - it will get 1 Check In then moves to the 2nd loop - will get the Check Out after getting the Check In and Check Out.. it will now go to the 3rd/last loop - which will be the one who will get the Breaks(the one with the Out Exception) using the range of the result of the 1st and 2nd loop.

I am doing this so I can calculate every pair of Out and Outbreak in every one(1) shift(ranging from Check-In to Check-Out). And after that, so I can calculate the total minutes/hour of the break for one(1) shift.

I cannot just based the range of the shift through Date. becoz if i do, the other breaks will be on the other/next shift which has the same Check-In date as the Break Date and I cannot calculate it properly or count the every pair of break it has.

but it seems i cannot get it right. What should i do in order to have the right result? or get the loop right...

<?php
include 'connection.php';
$checktime='';
$outtime = '';

$isql = mysql_query("Select * from dtr where ACNo = '1220' and 
State = 'Check In'") or die(mysql_error());

while($irow = mysql_fetch_array($isql)){
    $iID = $irow['ACNo'];
    $iDate = $irow['Date'];
    $iTime = $irow['Time'];
    $iState = $irow['State'];

    if($iState == 'Check In'){
        $checktime = $iTime;
        $indate = $iDate;
    }

$osql = mysql_query("Select * from dtr where ACNo = '1220' and 
State = 'Check Out'") or die(mysql_error());

while($orow = mysql_fetch_array($osql)){
    $oID = $orow['ACNo'];
    $oDate = $orow['Date'];
    $oTime = $orow['Time'];
    $oState = $orow['State'];

    if($oState == 'Check Out'){
        $outtime = $oTime;
        $outdate = $oDate;
    }


$bsql = mysql_query("select * from dtr where Exception = 'Out' And Time Between     
'$checktime' and '$outtime'")or die(mysql_error());

while($brow = mysql_fetch_array($bsql)){
    $bID = $brow['ACNo'];
    $bDate = $brow['Date'];
    $bTime = $brow['Time'];
    $bState = $brow['State'];

    echo $bDate.' - ';
    echo $bTime.' ';
    echo $bState.'<br>';
}

}

}
?>

edited: as requested.. the output that i've been expecting or wanting is like this...

 5/13/2013 | 11:45:18 PM  |  Check In  
 5/14/2013 | 12:46:58 AM  |  Out       
 5/14/2013 | 12:52:41 AM  |  Out Back  
 5/14/2013 | 02:12:50 AM  |  Out       
 5/14/2013 | 02:43:11 AM  |  Out Back  
 5/14/2013 | 05:46:58 AM  |  Out       
 5/14/2013 | 06:22:41 AM  |  Out Back  
 5/14/2013 | 06:55:12 AM  |  Check Out
 Break Count : 3
 Total Mins Of Break: [total here]

 5/14/2013 | 11:47:13 PM  |  Check In  
 5/15/2013 | 12:36:28 AM  |  Out       
 5/15/2013 | 12:59:11 AM  |  Out Back  
 5/15/2013 | 03:12:54 AM  |  Out       
 5/15/2013 | 03:33:31 AM  |  Out Back  
 5/15/2013 | 06:55:12 AM  |  Check Out
 Break Count : 2
 Total Mins Of Break: [total here]

Post what you would like to see in tabular form (the same way as posted sample data) after your calculation. — peterm, Jun 20 '13 at 05:42
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://www.brightmeup.info/article.php?a_id=2). — NullPoiиteя, Jun 20 '13 at 06:12
Ok. but i dont want to change to MySQLi yet. I just want to solve this problem first then i can get an idea what's wrong about my codes/query.. and will try using Mysqli. — Steve, Jun 20 '13 at 07:17
possible duplicate of [PHP file cannot enter some part of code](http://stackoverflow.com/questions/11575531/php-file-cannot-enter-some-part-of-code) — hakre, Jun 20 '13 at 07:43

score 1 · Accepted Answer · answered Jun 20 '13 at 07:22

The algorithm you have is fragile, tough to understand (perhaps due to the variable names and nested looping), and easily optimized. I would suggest a different approach. This will produce the example output you wanted and fix the issues I mentioned:

<?php
include 'connection.php';

$q = mysql_query("SELECT * FROM dtr WHERE ACNo = '1220' ORDER BY `Date`, `Time`") or die(mysql_error());

$isIn = false;
$breakDate = NULL;
$breakTime = NULL;
$breakCount = 0;
$breakCumulativeSeconds = 0;
while($dtr = mysql_fetch_object($q))
{
  $logLine = "$dtr->Date | $dtr->Time | $dtr->State\n";

  switch($dtr->State)
  {
    case 'Check In':
      if($isIn)
      {
        throw new LogicException('Two check-ins.');
      }

      $isIn = true;
      echo $logLine;
      break;

    case 'Check Out':
      if(!$isIn)
      {
        throw new LogicException('Check out when not checked in');
      }
      else if(isset($breakDate))
      {
        throw new LogicException('Check out while on break.');
      }

      echo $logLine;
      echo "Break Count: $breakCount\n";
      echo 'Total Mins Of Break: ', number_format($breakCumulativeSeconds / 60, 2), "\n\n";
      $breakCount = $breakCumulativeSeconds = 0;
      $isIn = false;
      break;

    case 'Out':
      if(!$isIn)
      {
        throw new LogicException('Break when not checked in');
      }
      else if(isset($breakDate))
      {
        throw new LogicException('Break start while already on break');
      }

      $breakDate = $dtr->Date;
      $breakTime = $dtr->Time;

      echo $logLine;
      break;

    case 'Out Back':
      if(!isset($breakDate))
      {
        throw new LogicException('Break end while not on break');
      }

      ++$breakCount;
      $breakCumulativeSeconds += (strtotime("$dtr->Date $dtr->Time") - strtotime("$breakDate $breakTime"));
      $breakDate = $breakTime = NULL;

      echo $logLine;
      break;

    default:
      throw new LogicException('Unknown State.');
  }
}
?>

Also, like someone mentioned, you should really consider using mysqli, PDO, or better yet a full-fledged ORM like Doctrine or ActiveRecord like what Yii has. You will be very happy if you do any of those.

i really like your answer Ezikiel! it really works.. but the problem now is in me. im not familiar with using case. lol! anyways i will study the given codes above and try to figure out how i can apply this one to the system i am working on. thank you, man! — Steve, Jun 20 '13 at 07:52

score 1 · Answer 2 · answered Jun 20 '13 at 19:08

What I would probably do is to make all calculations and prepare data on DB side (with rather brutal query) and then present it in php

$acno = 1220;

$sql = 
"SELECT s.n, s.details, b.breaks_count, b.breaks_total
  FROM
(
  SELECT n, GROUP_CONCAT(CONCAT_WS('|', DATE_FORMAT(date, '%m/%d/%Y'), time, state)) details
    FROM
  (
    SELECT @n := IF(state = 'Check In', @n + 1, @n) n, d.*
      FROM dtr d, (SELECT @n := 0, @m := 0) n
     WHERE acno = $acno
  ) a
  GROUP BY n
) s JOIN
(
  SELECT n, COUNT(*) breaks_count, ROUND(SUM(o.secs) / 60) breaks_total
    FROM
  (
    SELECT n, m 
           ,TIME_TO_SEC(
            TIMEDIFF(MIN(CASE WHEN state = 'Out Back' THEN ADDTIME(date, time) END),
                     MIN(CASE WHEN state = 'Out'      THEN ADDTIME(date, time) END))) secs
      FROM
    (
      SELECT @n := IF(state = 'Check In', @n + 1, @n) n, 
             @m := IF(state = 'Out', @m + 1, @m) m,
             d.*
        FROM dtr d, (SELECT @n := 0, @m := 0) n
       WHERE acno = $acno
    ) q
     WHERE state IN('Out', 'Out Back')
     GROUP BY n, m
  ) o
   GROUP BY n
) b ON s.n = b.n";

$result = mysql_query($sql);
if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}
//We've done everything on db side so presentation is short and clean
while($row = mysql_fetch_assoc($result)) {
    $details = explode(',', $row['details']);
    foreach ($details as $detail) {
        list($date, $time, $state) = explode('|', $detail);
        echo "$date - $time - $state<br>";
    }
    echo "Break Count: " .$row['breaks_count']."<br>";
    echo "Total Mins Of Break: ".$row['breaks_total']."<br>";
}

Here is SQLFiddle demo for db query.

Output of php script based on your sample data:

05/13/2013 - 11:45:18 PM - Check In
05/14/2013 - 12:46:58 AM - Out
05/14/2013 - 12:52:41 AM - Out Back
05/14/2013 - 02:12:50 AM - Out
05/14/2013 - 02:43:11 AM - Out Back
05/14/2013 - 05:46:58 AM - Out
05/14/2013 - 06:22:41 AM - Out Back
05/14/2013 - 06:55:12 AM - Check Out
Break Count: 3
Total Mins Of Break: 72
05/14/2013 - 11:47:13 PM - Check In
05/15/2013 - 12:36:28 AM - Out
05/15/2013 - 12:59:11 AM - Out Back
05/15/2013 - 03:12:54 AM - Out
05/15/2013 - 03:33:31 AM - Out Back
05/15/2013 - 06:55:12 AM - Check Out
Break Count: 2
Total Mins Of Break: 43

Thank you for helping @peterm! But.... im just a newbie in PhP and MySQL. I found your code so hard to understand. thanks man — Steve, Jun 21 '13 at 04:53

Getting Range of record using a Nested While Loop MySQL/PHP

2 Answers2