php - Check the latest update data and only update date run in another query

Question

I am working with attendance management system where data is received from finger print scanner. Shown below is the structure of

Attendance Table:

EnrolledID  Date    Time    
2603    2019-08-01  04:29:52    
2087    2019-08-01  04:46:12    
2539    2019-08-01  05:44:31    
1310    2019-08-01  05:48:25    
2088    2019-08-01  05:48:32    
112     2019-08-01  05:50:58    
672     2019-08-01  05:54:17    
108     2019-08-01  05:54:36

Using Php code, The data is split into time_in and time_out

$query = "SELECT 
EnrolledID,Date,COUNT(Time),
CASE 
WHEN COUNT(Time) = 1 THEN 
    CASE 
        WHEN Time > '03:00:00' 
            && Time <= '12:15:00' 
            THEN Time END
ELSE MIN(Time) 
END as time_in,
CASE 
WHEN COUNT(Time) = 1 THEN
    CASE 
        WHEN Time > '12:15:00' 
            && Time <= '23:59:59' 
            THEN Time END
ELSE NULLIF(MAX(Time), MIN(Time)) 
END as time_out
from attendance
GROUP BY EnrolledID,Date";


$result_set = mysqli_query($conn, $query);

if (mysqli_num_rows($result_set) > 0) 
          {
              //$query = "TRUNCATE TABLE month_data";
              //mysqli_query($conn, $query);

             while($row = mysqli_fetch_assoc($result_set)) 
             {

                 $EnrolledID = sprintf('%04d',$row["EnrolledID"]);

                 $Date = date_format( date_create($row['Date']), 'Y-m-d D' );

                 if(isset($row['time_in']) == 'NULL')
                 {
                     $time_in = date_format( date_create($row['time_in']), 'H:i:s' );
                 }
                 else
                 {

                     $time_in = '';
                 }

                if(isset($row['time_out']) == 'NULL')
                {
                   $time_out = date_format( date_create($row['time_out']), 'H:i:s' );

                }
                else{
                     $time_out = ''; 
                }

                $totaltime = (strtotime($time_out) - strtotime($time_in));

                $hours = sprintf('%02d', intval($totaltime / 3600));

                $seconds_remain = ($totaltime - ($hours * 3600)); 

                $minutes = sprintf('%02d', intval($seconds_remain / 60));   
                $seconds = sprintf('%02d' ,($seconds_remain - ($minutes * 60)));

                $final = '';

                if ($time_in == '' || $time_out == '')
                {
                    $final = '';
                }
                else
                {
                    $final .= $hours.':'.$minutes.':'.$seconds;
                }

                 $sql = "INSERT INTO month_data (Emp_no, Date,Time_in,Time_out,Total_hours) VALUES ({$EnrolledID},'{$Date}','{$time_in}','{$time_out}','{$final}')";
                 mysqli_query($conn, $sql);
             }
                 echo "successfully Process data <br>";
          }

I need to do this daily without updating the old data. (Note Count is not equal as month data since the employee may swipe more time a da)

Expected Output in table

Emp_no  Date    Time_in Time_out    Total_hours TranID  
23  2019-08-01  07:54:40    16:01:40    08:07:00    1   
23  2019-08-02  07:42:35    16:02:53    08:20:18    2   
23  2019-08-03  07:37:41    16:11:51    08:34:10    3   
23  2019-08-05  07:48:32    16:02:48    08:14:16    4   
23  2019-08-06  07:37:16    16:00:22    08:23:06    5   
23  2019-08-07  07:30:31    16:00:14    08:29:43    6   
23  2019-08-09  07:44:11    16:00:25    08:16:14    7

Answer 1

Whenever you run the script. Try to select data for the particular date using sort by

$dateToday = date('Y-m-d')

so that old data will not be affected.