'insert zk log check in and out to one row in database

I am retrieving data from ZK attendance device using zkteco-sdk-php.

The out come is like this

UID ID NAME STATE DATE TIME TYPE
55 275 Employ1 Fingerprint 19-06-2021 08:14:16 Check-in
55 275 Employ1 Fingerprint 19-06-2021 11:15:20 Check-out

*and all the data is not sorted by user its sorted by the first finger print attendance so the log is mixed up with different users and different type of attendance

I want to insert the retrieved data to database the issue is the data is being retrieved each log (check in OR check out) as a single row I want to insert the same day (check in && check out for the same user in the same row )

this is what I am aiming for

ID user ID Check-in Check-out
55 275 19-06-2021 08:14:16 19-06-2021 11:15:20

this is the code for retrieving the data from zk

                    $attendance = $zk->getAttendance();
                    if (count($attendance) > 0) {
                        $attendance = array_reverse($attendance, true);
                        sleep(1);
                        foreach ($attendance as $attItem) {
                            ?>
                            <tr>
                                <td><?php echo($attItem['uid']); ?></td>
                                <td><?php echo($attItem['id']); ?></td>
                                <td><?php echo(isset($users[$attItem['id']]) ? $users[$attItem['id']]['name'] : $attItem['id']); ?></td>
                                <td><?php echo(ZK\Util::getAttState($attItem['state'])); ?></td>
                                <td><?php echo(date("d-m-Y", strtotime($attItem['timestamp']))); ?></td>
                                <td><?php echo(date("H:i:s", strtotime($attItem['timestamp']))); ?></td>
                                <td><?php echo(ZK\Util::getAttType($attItem['type'])); ?></td>
                            </tr>
phpmysqldata-structureszkteco


Solution 1:[1]

MySQL:

SELECT
   t1.ID,
   t1.UserID,
   ADDTIME(CONVERT(t1.`date`, DATETIME), t1.`time`) as "Check-in",
   ADDTIME(CONVERT(t2.`date`, DATETIME), t2.`time`) as "Check-out"
FROM table t1
LEFT JOIN table t2 ON t2.ID=t1.ID 
                  and t2.UserID=t1.UserID
                  and t2.Type = 'Check-out'
WHERE 
   ID = 55
   and UserID = 275
   and Type = 'Check-in'

NOTE: You need to replace table with the correct table name.

Different examples on how to use this SQL query in PHP can be found in a lot of places, like: Google: how to query MySQL from PHP

Solution 2:[2]

You should to store logs "as is" - separate rows to check-in and check-out and on select join two rows by UUID and ID:

select 
    log_in.uuid,
    log_in.id,
    log_in.name,
    log_in.state,
    log_in.event_time as log_in_time,
    log_out.event_time as log_out_time
from zk_log log_in
join zk_log log_out on log_in.uuid = log_out.uuid and log_in.id = log_out.id
where log_in.event_type = 'check-in' and log_out.event_type = 'check-out';

Solution 3:[3]

I think it can be achieved like this:

SELECT UID,
       ID,
       CONCAT(date,' ',MIN(time)) AS Check_in,
       CONCAT(date,' ',MAX(time)) AS Check_out
FROM attendance
GROUP BY date, UID, ID;

But I have to address that the date data in your example is not standard MySQL DATE datatype and if the destination table that you want to insert to have structure like:

destination_table 
(UID INT, 
ID INT,
Check_in DATETIME,  <--- this datatype
Check_out DATETIME); <---

the insert will not be successful and will return an error:

Incorrect datetime value: '19-06-2021 08:14:16' for column 'Check_in' at row 1

Otherwise, if the destination_table datatype is VARCHAR(), it won't have problem inserting. However, I highly encourage using the standard format at least on the destination table. So if the source table really stores date as VARCHAR() and the destination table is using DATETIME datatype, you'll need STR_TO_DATE():

INSERT INTO destination_table
SELECT UID,
       ID,
       CONCAT(STR_TO_DATE(date,'%d-%m-%Y'),' ',MIN(time)) AS Check_in,
       CONCAT(STR_TO_DATE(date,'%d-%m-%Y'),' ',MAX(time)) AS Check_out
FROM attendance
GROUP BY date, UID, ID;

Here's a demo fiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk
Solution 2 Slava Rozhnev
Solution 3 FanoFN

Related Questions