chris bahr chris bahr - 1 month ago 8
MySQL Question

Export MySQL table while inserting/updating each row to mark it as "exported" or equivalent

I can use PHPMyAdmin or my current PHP script using a Cron Job to Export my table into a csv file just fine, but i'm trying to get it to where these rows cannot be exported again. My idea was to just update or insert a value in a column to mark it as "exported" and try to find a way to only export rows not marked as such. This is my export PHP script if it helps. Thanks

<?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();


$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);
?>

Tim Tim
Answer

First, add boolean field called "exported" to the table with a default value of FALSE (0).

Second, change the SELECT to only select records where exported is false.

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

Third, UPDATE that record's "exported" field to TRUE (1) after writing it the CSV.

$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));

All together now:

<?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);
?>