CODEWITHSUNDEEP
phpmysql
Derek
Derek

Reputation: 5037

Calculating time from timesheet in while statement PHP/MYSQL

I am having a heck of a time with this. I have been looking and I see different things I could use like CAST, date() function, SUM, Javascript Math, etc. Problem is I dont know which one to use or which one I would use in my case. The more I read different threads and "solutions" the more I get confused on how to apply this to my needs.

I have a simple timesheet table in my database called "timesheet" in it I have the columns entry_id (AI), user_id, firstname, lastname, punch (datetime) and comment (in/out). I have done a simple query in PHP to bring in the users timesheet into an HTML table like below. timesheet table HTML

In my PHP query I am running a WHILE statement to loop through the records. Because I am using a loop I have no idea how I can/would calculate the time to give an overall total of hours worked for that time period.

Here is my query:

$timesheet = mysqli_query($connect, "SELECT DATE_FORMAT(punch, '%Y/%m/%d') AS punch_date, DATE_FORMAT(punch, '%H:%i:%s') AS punch_time, comment
                    FROM timesheet WHERE user_id = '$employee' AND punch BETWEEN '$fromDate' AND '$toDate'") or die('Error: ' . mysqli_error($connect));

if(mysqli_num_rows($timesheet)>0){
    $output = '<table class="table table-striped">
                  <thead>
                    <tr>
                      <th>Date</th>
                      <th>Time</th>
                      <th>Status</th>
                    </tr>
                  </thead>
                  <tbody>';
    while ($row = mysqli_fetch_array($timesheet, MYSQL_ASSOC)) {

        $date = $row['punch_date'];
        $time = $row['punch_time'];
        $status = $row['comment'];

        $output .= '<tr>
                      <td>' . $date . '</td>
                      <td ';
        if($status === 'In'){ 
            $output .= 'class="in-time"'; 
        } elseif($status === 'Out') {
            $output .= 'class="out-time"';
        }

        $output .= '>' . $time . '</td>
                      <td>' . $status . '</td>
                    </tr>';

    } //end while

    $output .= '</tbody>
                </table>';


    echo $output;

} //end if mysqli_num_rows

Is it even possible to calculate the time in a loop? If so would you mind giving an example and of not could you point me on the direction of where/what I am doing wrong?

UPDATE: Each time entry has either In or Out as a status, so I need to get the difference of each set of In and Out, then add that together to get the total number of time worked for that time period. Ultimately this is what I need to accomplish (see Total in red at bottom of table) Hopeful outcome

An here is a snapshot of the database table "timesheet" database

Upvotes: 1

Views: 2063

Answers (1)

tliokos
tliokos

Reputation: 3636

Before the while() we have to decide if we will use all rows to calculate the total time or not.

Because there is a catch here. What if a user has logged in but hasn't logged out yet?

In this case we will not use the last logged in time in our total. So:

$iterations = mysqli_num_rows($timesheet) % 2 == 0 ? mysqli_num_rows($timesheet) : mysqli_num_rows($timesheet) - 1;

Inside your while loop, convert punch_time to seconds.

while() {

...

$parts = explode(':', $row['punch_time']);

$seconds = (int) $parts[0] * 3600 + (int) $parts[1] * 60 + (int) $parts[2];

if($status === 'In'){ 

   $output .= 'class="in-time"'; 

   $total -= $iterations ? $seconds : 0; // Subtract from total when in and there is out coming

} else if($status === 'Out') {

   $output .= 'class="out-time"';

   $total += $seconds; // Add  to total when out
}

...

$iterations--;

}

After the loop you can transform seconds to the format you want by using 

$hours = floor($total / 3600);

$minutes = floor(($total / 60) % 60);

$seconds = $total % 60;

echo "$hours:$minutes:$seconds";

Upvotes: 2

Related Questions