Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts

    How do I structure this WHILE loop to get the intended output ??

    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
    Code:
    <table>
      <tr>
        <td>{inTime}</td>
        <td> - </td>
        <td>{outTime}</td>
        <td>employee</td>
      </tr>
      <tr>
        <td>{inTime}</td>
        <td> - </td>
        <td></td>
        <td>employee</td>
      </tr>
      <tr>
        <td></td>
        <td> - </td>
        <td>{outTime}</td>
        <td>employee</td>
      </tr>
      ...
    </table>
    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.
    PHP Code:
    while($row mysql_fetch_array($resultMYSQL_ASSOC)){
        
    $unixTime=strtotime($row['dateTime']);  // go back to a universal format
        
    $time=date('g:i:s A'$unixTime);  // the time (in 12hr format)
        
    $empId=$row['empId'];
        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?

    Please,
    ~ Mo

    NOTE: If additional info can help clarify any hazy points, I'd be happy to help you help me. Thanks again.
    Last edited by mOrloff; 11-08-2010 at 06:35 PM.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,500
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    Do you also have rows with "out times" on them?
    How do you know what the "out times" are?

    Also:
    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

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    isIN is a bool.
    Out times are identified where isIn is FALSE.

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

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,500
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    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.

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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).
    1. id
    2. empID (a FK)
    3. dateTime
    4. 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

    Edit:
    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:
    PHP Code:
    (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')
    UNION
    (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.
    Last edited by mOrloff; 11-08-2010 at 09:26 PM. Reason: Added more info

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,500
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    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.

    sorry.

  • #7
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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:
    Quote Originally Posted by mOrloff View Post
    ... I'm trying to break 2; ... I'm not getting ANY print ...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •