我正在从MS访问MySql数据库检索考勤日志。我想获得/检索仅从MS访问数据源和上传到MySql数据库的新记录(自动是首选)。下面是我现在的代码，请帮忙:
<?php
// Connect to MySql Local Database
$con = mysqli_connect("localhost", "root", "");
// Connect to Microsoft Access Using ODBC Driver
$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:'Program Files (x86)'ZKTeco'att2000.mdb", "", "");
// Check if Connect works
if($connection){
echo odbc_autocommit($connection);
$QUERY = odbc_exec($connection, "SELECT * FROM `CHECKINOUT`") or die(odbc_error($connection));
//If have records in MS Access Data Source
if(odbc_fetch_row($QUERY) > 0){
while ($ATT = odbc_fetch_array($QUERY)) {
$result = $ATT["CHECKTIME"];
// Here I want to Insert only New Records from MS Access
$INSERT = mysqli_query($con, "INSERT INTO `odbc`.`attendance` (`DATETIME`, `DATE`, `TIME`) VALUES ('$result', '$result', '$result')") or die(mysqli_error($con));
}
}
echo odbc_commit($connection);
}else{
echo "False";
}
?>
考虑使用临时表attendance_temp，它在结构上与最终表attendance完全相同，但每次运行脚本时都会定期清理:
mysqli_query($con, "DELETE FROM `odbc`.`attendance_temp`"); # OUTSIDE WHILE LOOP
...
$INSERT = mysqli_query($con,
"INSERT INTO `odbc`.`attendance_temp` (`DATETIME`, `DATE`, `TIME`)
VALUES ('$result', '$result', '$result')")
一旦临时表被填充，然后运行NOT IN/NOT EXISTS/LEFT JOIN…从临时表到最终表的IS NULL查询，以避免最终表和目标表中的重复记录:
INSERT INTO odbc.`attendance` (`DATETIME`, `DATE`, `TIME`)
SELECT t.`DATETIME`, t.`DATE`, t.`TIME`
FROM `odbc`.`attendance_temp` t
LEFT JOIN `odbc`.`attendance` f
ON t.`DATETIME` = f.`DATETIME`
WHERE f.`DATETIME` IS NULL;