Find values from SQL Query and calculate "early-ou"t field grand total using PHP

[ahyas]

The output from SQL Query is as expected below, except for the Grand total. I expect value as 00:52 as correct result instead of 4166862:56. Please guide me out! The rest of the code is in the attachment.

 

uid      dates             time_in  time_out late_in  early_out

10040 2017-03-01    00:00    00:00      00:00   00:00

10040 2017-03-02    07:18    15:50      00:03   00:10

10040 2017-03-03    07:58    11:21      00:43   00:00

10040 2017-03-04    00:00    00:00      00:00   00:00

10040 2017-03-05    00:00    00:00      00:00   00:00

10040 2017-03-06    00:00    00:00      00:00   00:00

10040 2017-03-07    00:00    00:00      00:00   00:00

10040 2017-03-08    08:00    15:36      00:45   00:24

10040 2017-03-09    00:00    00:00      00:00   00:00

10040 2017-03-10    06:55    11:42      00:00   00:00

10040 2017-03-11    00:00    00:00      00:00   00:00

10040 2017-03-12    00:00    00:00      00:00   00:00

10040 2017-03-13    00:00    00:00      00:00   00:00

10040 2017-03-14    00:00    00:00      00:00   00:00

10040 2017-03-15    00:00    00:00      00:00   00:00

10040 2017-03-16    07:38    16:01      00:23   00:00

10040 2017-03-17    07:34    12:05      00:19   00:00

10040 2017-03-18    00:00    00:00      00:00   00:00

10040 2017-03-19    00:00    00:00      00:00   00:00

10040 2017-03-20    00:00    00:00      00:00   00:00

10040 2017-03-21    08:16    15:51      01:01   00:09

10040 2017-03-22    00:00    00:00      00:00   00:00

10040 2017-03-23    00:00    00:00      00:00   00:00

10040 2017-03-24    07:18    10:58      00:03   00:02

10040 2017-03-25    00:00    00:00      00:00   00:00

10040 2017-03-26    00:00    00:00      00:00   00:00

10040 2017-03-27    00:00    00:00      00:00   00:00

10040 2017-03-28    00:00    00:00      00:00   00:00

10040 2017-03-29    07:04    15:53      00:00   00:07

10040 2017-03-30    00:00    00:00      00:00   00:00

10040 2017-03-31    08:04    11:26      00:49   00:00

Grand Total                                                      4166862:56 --> wrog result (Should be  00:52)

[ahyas]

Sorry for missing attachment

kuda.php

Edited July 15, 2017 by ahyas

[Jacques1]

Reading helps.

• Read the forum rules on how to properly post code.
• Read the PHP manual on what functions like strtotime() actually do. Hint: There's a difference between a time duration like “15 seconds” and a point in time like “2017-07-15 13:00:15 GMT”. Your SQL data types also need to reflect that. Right now, it looks like those are all generic VARCHARs with no type safety whatsoever.

[ginerjm]

show us the code that does your 'date math'.

[ahyas]

<?php
include('koneksi.php');
$sql=mysql_query("$sql=mysql_query("SELECT uid, dates, time_in, time_out,
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(time_in)-
TIME_TO_SEC('07:15:00'))),'%H:%i') AS late_in, //when time_in is greater than 07:15:00 calculate late_in
TIME_FORMAT(SEC_TO_TIME(SUM(CASE WHEN DAYOFWEEK(dates)=6 THEN //when day of dates is Friday time_out is 11:00. Else 16:00
TIME_TO_SEC('11:00:00')-TIME_TO_SEC(time_out) ELSE
TIME_TO_SEC('16:00:00')-TIME_TO_SEC(time_out) END)),'%H:%i') AS early_out
FROM tb_attendance WHERE uid=10040 AND dates BETWEEN '2017-02-28'
AND '2017-03-31' GROUP BY dates");

echo"<table border=1>
<tr><td>uid</td><td>dates</td><td>time_in</td><td>time_out</td><td>late_in</td><td>early_out</td></tr>";
$tally=0;

while($row=mysql_fetch_array($sql)){
echo"<tr><td>".$row['uid']."</td>
<td>".$row['dates']."</td>";

$def=date('H:i',strtotime('00:00:00')); 

if($row['time_in']==$row['time_out']){
echo"<td>".$def."</td>
<td>".$def."</td>";
echo"<td>".$def."</td>
<td>".$def."</td>";
}else{
echo"<td>".$row['time_in']."</td>
<td>".$row['time_out']."</td>";
if($row['late_in']>$def){
echo"<td>".$row['late_in']."</td>";
}else{
echo"<td>".$def."</td>";
}

if($row['early_out'] > $def){
echo"<td>".$row['early_out']."</td>";

}else{
echo"<td>".$def."</td>"; 
}

//Calculate grand total early out
$seconds = strtotime($row['early_out']);
$tally += $seconds;
// human format 
$hours = gmdate("H", $seconds);
$minutes = gmdate("i", $seconds);

}
}
$hours = floor($tally / 3600);
$minutes = gmdate("i", $tally);

echo"<tr><td align='center' colspan=4>Grand Total : </td><td></td>
<td>".$hours.":".$minutes."</td></tr></table>";

Here is my codes. 

Edited July 15, 2017 by ahyas

[Jacques1]

Congratulations, you've found the code tags. Now let's try to read the replies.

[ginerjm]

1 - Why are you using the mysql_* functions?  Stop using them.  Read The Manual.

2 - What is this supposed to be:

 

$sql=mysql_query("$sql=mysql_query("SELECT uid, dates, time_in, .....

[gizmola]

You are making things a lot harder than you need to.

 

Inside the loop you are doing this:

 

//Calculate grand total early out
$seconds = strtotime($row['early_out']);
$tally += $seconds;
// human format 
$hours = gmdate("H", $seconds);
$minutes = gmdate("i", $seconds);

There is no point in trying to make these values into a timestamp and a REAL date. You already have the format of HH:MM. Simply parse the string and keep the totals in 2 separate variables for hours and minutes.

 

Just before the start of your fetch loop initialize 2 variables:

 

$hours = $minutes = 0;

while($row=mysql_fetch_array($sql)){

Inside the loop remove all of the code I quoted earlier and replace with this instead:

 

    list($hour, $minute) = explode(':', $row['early_out']);
    $hours += $hour;
    $minutes += $minute;

Now you are left with doing a final calculation and display of the hours:minutes

 

Replace this code:

 

$hours = floor($tally / 3600);
$minutes = gmdate("i", $tally);

With this:

 

// Add Hours from minutes
$hours += floor($minutes / 60);
// Calc remaining minutes
$minutes = $minutes % 60;

echo"<tr><td align='center' colspan=4>Grand Total : </td><td></td>
<td>" . sprintf("%02d:%02d", $hours, $minutes) . "</td></tr></table>";

Here is an eval that simulates the code and should help you understand it.

[Jacques1]

You can and should calculate the sum in the query itself.

 

First, fix your data types. Times are stored in TIME columns (who would have thought it?). Then calculate the sums with a single query:

SELECT
    dates,
    SEC_TO_TIME(
        SUM(
            TIME_TO_SEC( TIMEDIFF(time_in, :regular_time_in) )    -- warning: this can lead to negative values
        )
    ) AS total_late_in,
    SEC_TO_TIME(
        SUM(
            TIME_TO_SEC(
                CASE
                WHEN DAYOFWEEK(dates) = 6 THEN TIMEDIFF(:regular_friday_time_out, time_out)    -- warning: this can lead to negative values
                ELSE TIMEDIFF(:regular_time_out, time_out)                                     -- this as well
                END
            )
        )
    ) AS total_early_out
FROM
    tb_attendance
WHERE
    uid = :uid
    AND dates BETWEEN :start_date AND :end_date

If MySQL wasn't too stupid to sum intervals, we wouldn't need those time-to-seconds-to-time gymnastics, but that's how it is.

 

Some very important comments:

• The mysql_* functions are dead. Stonedead. They've already been removed from current PHP versions, so the next server update can kill your entire application. It's the year 2017, and we use PDO now.
• Don't hard-code user IDs or dates into your query. Pass them as parameters to a prepared statement – I've already included those parameters in the above query.
• You cannot select columns which aren't in the GROUP BY clause. This is nonsensical and simply not valid SQL. If your database system accepts this nonetheless, you need to fix the configuration.
• Time differences can be negative. You have to either rule out this case or handle it. If there can be multiple entires per date, things become even more complicated, because simple time comparisons don't work. It generally looks like you haven't really thought out this whole thing.