Handling multiple records with same ID, inside a foreach loop PHP-CodePudding

This is an attendance management System, and let me explain how it is structured. There are two tables here, One is tbl_temperory & Other is tbl_review,

tbl_temperory

Autoid | emp_id | att_date | att_time
-------------------------------------
100    | 05     |05/25/2022| 07.30
101    | 05     |05/25/2022| 09.30
102    | 05     |05/25/2022| 18.30
103    | 06     |05/25/2022| 08.30
104    | 06     |05/25/2022| 20.30

tbl_review

Autoid | emp_id | att_date | clock_in | clock_out
-------------------------------------------------
200    | 05     |05/25/2022| 18.30    |
201    | 06     |05/25/2022| 08.30    | 20.30

When the user clocked in or clocked out initial record updates to the temporary table, sometimes the user can clock in and clock out multiple times, So the Temporary table might have multiple records for the same user.

During Day End there is a option to fetch the Attendance Record, and it updates to tbl_review.

Now the problem here is, When there is only two records for an employee, tbl_review updates the clock_in & clock_out percfectly. But when there is multiple Records, it updates only the last record time as clock_in time.

I have below attached the foreach loop how it updates the review table, Can someone guide me, where I am making the Mistake.

Thank you.

$temp = All records from temp table Stored here
 if ($temp) { 
   $i = 0;
   $employee = '';
   $continue = FALSE;
   foreach ($temp as $row) {
    if ($continue) {   
      $continue = FALSE;
      $i  ;
      continue;
    }
    $employee = $row['emp_id'];
    $attendanceDate = $row['att_date'];
    $clockIn = $row['att_time'];
    $nextKey = $i   1;
    $clockOut = NULL;
     if (array_key_exists($nextKey, $temp)) {
        if ($temp[$nextKey]['emp_id'] == $row['emp_id'] && $attendanceDate == $temp[$nextKey]['att_date']) {
                    $clockOut = $temp[$nextKey]['att_time'];
                    $continue = TRUE;
                }

    }
  }
 }

CodePudding user response：

Actually, you can do this with a single insert query. The clock_in time for a user/employee will be the least punch time among all and clock_out will be the maximum of them all for a given date.

So, you can construct a query with PDO and inject the current date at hand via prepared statements into the below query:

insert into tbl_review(emp_id, att_date,clock_in, clock_out) 
SELECT emp_id, att_date , min(STR_TO_DATE(att_time, '%H.%i:%s')) as clock_in, max(STR_TO_DATE(att_time, '%H.%i:%s')) as clock_out
FROM `tbl_temperory`
where att_date = '05/25/2022' -- your date variable here
group by emp_id

CodePudding user response：

With similar logic to your codes, assuming that your records are simillar to the one you shows (ascending emp_id and att_time).

if ($temp) {
    $i = 0;
    $newID = TRUE;
    foreach ($temp as $row) {
        $employee = $row['emp_id'];
        $attendanceDate = $row['att_date'];
        $nextKey = $i   1;
        
        if ($newID) {
            $clockIn = $row['att_time'];
            addClockInToTblReview($employee, $attendanceDate, $clockIn);
        }
        
        if (array_key_exists($nextKey, $temp)) {
            if ($temp[$nextKey]['emp_id'] != $row['emp_id'] || $attendanceDate != $temp[$nextKey]['att_date']) {
                $clockOut = $row['att_time'];
                addClockOutToTblReview($employee, $attendanceDate, $clockOut);
                $newID = TRUE;
            } else {
                $newID = FALSE;
            }
        }
        $i  ;
    }
}

The logic is to iterate every data entry,

if its the first entry with the same id and date, update clockIn
if next data entry is with a different id or different date, update clockOut
else do nothing and continue

ps. write your own addClockInToTblReview and addClockOutToTblReview as not much info regarding the db is provided