How can I add this to my code?

Question

Here is my code:
<?php $databasehost = "localhost"; 
$databasename = "import"; 
$databasetable = "import"; 
$databaseusername="username"; 
$databasepassword = "password"; 
$fieldseparator = ","; 
$lineseparator = "
";
$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::MYSQLATTRLOCALINFILE => true,
            PDO::ATTRERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}
$affectedRows = $pdo->exec("
      LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." 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.
";
?>
When I replace the LOAD DATA LOCAL INFILE command with the REPLACE command I get an error which makes me believe I am doing something wrong haha, I just need the script to replace the current database table with the CSV file instead of adding to the database, where do I add the REPLACE syntax?
Thanks :)

Iain Simmons · Answer

According to the MYSQL documentation for LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.1/en/load-data.html) the 'REPLACE' keyword comes between the filename and 'INTO TABLE'.
So your $affectedRows would be the following:
php
$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));

Welcome to the Treehouse Community

Looking to learn something new?

nicholas maddren

nicholas maddren

How can I add this to my code?

1 Answer

Iain Simmons

Iain Simmons