PHP-voorbeeld: biometrische aanwezigheid opslaan in MySQL

CREATE DATABASE IF NOT EXISTS cams_biometric;
USE cams_biometric;

CREATE TABLE IF NOT EXISTS attendance_logs (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_id       VARCHAR(50)  NOT NULL,
    log_time      DATETIME     NOT NULL,
    punch_type    VARCHAR(20)  DEFAULT NULL,
    input_type    VARCHAR(20)  DEFAULT NULL,
    temperature   DECIMAL(4,1) DEFAULT NULL,
    face_mask     TINYINT(1)   DEFAULT NULL,
    serial_number VARCHAR(50)  DEFAULT NULL,
    label_name    VARCHAR(100) DEFAULT NULL,
    raw_json      TEXT         DEFAULT NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (user_id, log_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
// Database config
$pdo = new PDO("mysql:host=localhost;dbname=cams_biometric;charset=utf8mb4", "root", "");

// Read incoming JSON
$rawInput = file_get_contents("php://input");
$data     = json_decode($rawInput, true);

// Parse PunchLog and insert
if (isset($data['RealTime']['PunchLog'])) {
    $log = $data['RealTime']['PunchLog'];
    $stmt = $pdo->prepare("
        INSERT INTO attendance_logs
            (user_id, log_time, punch_type, input_type, temperature, face_mask, serial_number, label_name, raw_json)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE punch_type=VALUES(punch_type), input_type=VALUES(input_type)
    ");
    $stmt->execute([
        $log['UserId']       ?? '',
        date('Y-m-d H:i:s', strtotime($log['LogTime'] ?? 'now')),
        $log['Type']         ?? null,
        $log['InputType']    ?? null,
        $log['Temperature']  ?? null,
        isset($log['FaceMask']) ? ($log['FaceMask'] ? 1 : 0) : null,
        $data['RealTime']['SerialNumber'] ?? null,
        $data['RealTime']['LabelName']    ?? null,
        $rawInput
    ]);
}

// Always respond with this
header("Content-Type: application/json");
echo '{"status":"done"}';
?>