View Full Version : How do I structure this WHILE loop to get the intended output ??

11-08-2010, 07:31 PM
My row data has three fields: empID, dateTime, isIn

The goal here is to get a report, per day, of all the activity broken into IN/OUT pairs.
The output should be like

<td> - </td>
<td> - </td>
<td> - </td>

Notice how it should just have empty values where a time-punch action was missed.

I have it working just fine in perfect-case scenarios, but the difficulty comes in when a user forgets to clock in or out.
How do I structure the loop to handle a missing value?

Here's the loop which currently works if there aren't any missing values.

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$unixTime=strtotime($row['dateTime']); // go back to a universal format
$time=date('g:i:s A', $unixTime); // the time (in 12hr format)
if($row['isIn']){ // if this is a Clock IN ...
$buffer.='<tr>'."\n"; // start a new row
$buffer.='<td>'.$time.'</td>'; // print the start time
$buffer.='<td>-</td>'; // include the seporator column
$start=$unixTime; // capture it as a "START" time
if($isComplete==1){ // if we're hitting our second IN in a row...
//...do not increment.
}else{ // otherwise...
$isComplete++; //...increment.
}elseif(!$row['isIn']){ // if this is a Clock OUT ...
$buffer.='<td>'.$time.'</td>'; // print the time
$stop=$unixTime; // capture it as a "STOP" time
$buffer.='<td>'.$empsAry["$empId"]["fName"].' '.$empsAry["$empId"]["mi"].' '.$empsAry["$empId"]["lName"].'</td>';// name
$buffer.='</tr>'."\n"; // end the row
$isComplete++; // increment
if($isComplete==2){ // if we have a complete in and out pair...
$seconds=($stop-$start); //...find the difference
$totalTime += $seconds; // (TODO: convert to hours(if applicable) & minutes, and...) add the time to the total for the day.
$isComplete=0; // since we're IN this ELSEIF, we must've made a successful in/out pair. Therefore reset.
} // end IF/ELSEIF($row['isIn'...
} // end WHILE($row...

As you can see, this has major weak points by having the opening and closing tags for the tablerow in the IF statements, but if I move them outside of those, they get hit each time the while loop does its thing, and then there are a bunch of extra <tr> tags all over the place. I need to cluster the output of my rows.
What if someone has two isIns or two !isIns in a row? How can change my loop to achieve the desired output?

~ Mo

NOTE: If additional info can help clarify any hazy points, I'd be happy to help you help me. Thanks again.

11-08-2010, 09:20 PM
Do you also have rows with "out times" on them?
How do you know what the "out times" are?

What is the format of isIN? Is that a UNIX timestamp stored as your "isIN" variable?
Same with "dateTime" ... is that a UNIX timestamp, like this: 1289251245

11-08-2010, 09:34 PM
isIN is a bool.
Out times are identified where isIn is FALSE.

dateTime is MySQL datetime. IE: 2010-11-08 13:34:16

11-08-2010, 09:46 PM
So each row actually has four fields?

empID, dateTime, isIn, isOut ???

There is a value in one or the other, but never both.
But sometimes they forget to punch out, so you have
a couple of "isIn" without any "isOut" between them?

Clarify exactly what variables you have in each MySQL table row.

11-08-2010, 10:13 PM
Thanks for your persistence.
There are a total of three pertinent fields in the MySQL table row (well, four if we want to include the unique id).

empID (a FK)
isIn (bool)

If isIn proves TRUE, then the time was for clocking IN.
If isIn equates to FALSE, then the time was for clocking OUT.

What else can I help clear up??
~ Mo

BTW, here's a copy of the query I use to get my results.
I hope it helps clarify things, rather than muddy the waters even further:

(SELECT id,empId,clockedDateTime AS dateTime,isIn
FROM times
WHERE modifiedDateTime IS NULL
AND clockedDateTime >= '$onDate 00:00:00'
AND clockedDateTime <= '$onDate 23:59:59')
(SELECT id,empId,modifiedDateTime AS dateTime,isIn
FROM times
WHERE modifiedDateTime IS NOT NULL
AND modifiedDateTime >= '$onDate 00:00:00'
AND modifiedDateTime <= '$onDate 23:59:59')
ORDER BY empId,dateTime

Please note that if a manager makes a time-correction, that goes into "modifiedDateTime". While this allows a statistic on how many corrections are made per employee (and therefore each employees accuracy ratings), all we need for this report is ONE date-time value per row. Hence, the UNION between the IS NULL and IS NOT NULL queries.

11-10-2010, 12:17 AM
I guess I've lost steam on this one ...

It's too hard to do without actually testing things out.
This forum is not a good venue for developing and testing coding.

I just have no way to duplicate the code, the database or the whole system.


11-10-2010, 12:28 AM
Hey, thanks anyhow.
I know that was kinda like throwing a big fish on a little pan, and your attention was greatly appreciated.

Anyhow, I'm trying another method of getting the desired results, and the hurdle I'm at seems much more forum appropriate.
Please take a look at:

... I'm trying to break 2; ... I'm not getting ANY print ...