How can I add a MYSQL TRUNCATE TABLE query to my PHP script?

Question

Hello, so I have this script:

<?php
$databasehost = "localhost"; 
$databasename = "import"; 
$databasetable = "import"; 
$databaseusername="import"; 
$databasepassword = "password"; 
$fieldseparator = ","; 
$lineseparator = "\n";
$csvfile = "test.csv";

if(!file_exists($csvfile)) {
    die("File not found. Make sure you specified the correct path.");
}

try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
      LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator));

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

I need this script to replace all data in the MYSQL table with the data in the CSV file.

Even known it has REPLACE INTO TABLE running it doesn't actually replace all of the data it just adds new rows to the table.

The obvious option is to run a TRUNCATE at the start of the script to wipe all of the data in the table and then replace it with the CSV the problem I am having is successfully implementing it into the script, any idea how this can be done?

Answer 1 · 2014-07-15T18:28:50Z

July 15, 2014 6:28pm

Have you tried adding

$sql = "TRUNCATE TABLE import";
$command = $pdo->prepare($sql);
$command->execute();

in the try section?

Welcome to the Treehouse Community

Looking to learn something new?

nicholas maddren

nicholas maddren

How can I add a MYSQL TRUNCATE TABLE query to my PHP script?

1 Answer

Alex Szabó

Alex Szabó