php - 在插入/更新每一行时导出 MySQL 表以将其标记为 "exported"或等效项

Question

我可以使用 PHPMyAdmin 或我当前使用 Cron 作业的 PHP 脚本将我的表导出到 csv 文件中，但我正在尝试将其导出到无法再次导出这些行的位置。我的想法是只在列中更新或插入一个值以将其标记为“已导出”，并尝试找到一种方法来仅导出未标记为此类的行。如果有帮助，这是我的导出 PHP 脚本。谢谢

<?php
 // mysql database connection details
    $host = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "database";

    // open connection to mysql database
    $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));

    // fetch mysql table rows
    $sql = "select * from product_sheets";
    $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));

$csv_fields=array();  // header array for the csv file

$csv_fields[]='header1';
$csv_fields[]='header2';


    $fp = fopen('/home/blog2696/public_html/productsheet/Exports/export.csv', 'w');

        fputcsv($fp, $csv_fields);

    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($fp, $row);
    }

    fclose($fp);

    //close the db connection
    mysqli_close($connection);
?>

Answer 1

首先，将名为“exported”的 bool 字段添加到表中，默认值为 FALSE (0)。

其次，将 SELECT 更改为仅选择导出为 false 的记录。

$sql = "select * from product_sheets where exported = 0";

第三，在写入 CSV 后将该记录的“导出”字段更新为 TRUE (1)。

$export_mark_sql = "UPDATE product_sheets SET exported = 1 WHERE primary_key= " . $row['primary_key'];
$export_mark_result = mysqli_query($connection, $export_mark_sql ) or die("Update Error " . mysqli_error($connection));

现在在一起:

<?php

// mysql database connection details
$host = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// open connection to mysql database
$connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));

// fetch mysql table rows for all records that haven't been exported already
$sql = "select * from product_sheets where exported = 0";
$result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));

$csv_fields=array();


$fp = fopen('/home/blog2696/public_html/productsheet/Exports/export.csv', 'w');

    fputcsv($fp, $csv_fields);

while($row = mysqli_fetch_assoc($result))
{
    // Mark this exported record as exported=true
    // -- Change "primary_key" below to the primary key field of the "product_sheets" table.
    $export_mark_sql = "UPDATE product_sheets SET exported = 1 WHERE primary_key= " . $row['primary_key'];
    $export_mark_result = mysqli_query($connection, $export_mark_sql ) or die("Update Error " . mysqli_error($connection));
    fputcsv($fp, $row);
}

fclose($fp);

//close the db connection
mysqli_close($connection);
?>