Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

nicholas maddren
nicholas maddren
12,793 Points

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

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?

1 Answer

Have you tried adding

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

in the try section?