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

[Nickmadd]

Hey guys, 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 I have been surfing the net for a while and cant find anything on this topic?

[Ch0cu3r]

The code uses PDO to interact with the database. To truncate table run your query using PDO::exec

[Nickmadd]

The code uses PDO to interact with the database. To truncate table run your query using PDO::exec

 

Hi I have tried adding this:

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

$pdo->exec("TRUNCATE TABLE;");

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

?>

As you can see I have tried adding 

$pdo->exec("TRUNCATE TABLE;");

Which I am guessing is completely wrong as I am getting an error ha, I am still new to PHP. Any idea how what I am doing wrong or how many things I am doing wrong?

Edited July 14, 2014 by Nickmadd

[Ch0cu3r]

 

As you can see I have tried adding 

$pdo->exec("TRUNCATE TABLE;");

 

You need to specify the table you are wanting to truncate

$pdo->exec("TRUNCATE TABLE `$databasetable`");

[Nickmadd]

 

You need to specify the table you are wanting to truncate

$pdo->exec("TRUNCATE TABLE `$databasetable`");

 

 

Thanks that is working great! I didn't know if I needed to specify the table seen as it is specified at the start of the script, can I be really cheeky and ask how I would go by skipping the first row? Would this work?

 

$pdo->exec("IGNORE 1 LINES `$databasetable`");

[Ch0cu3r]

Thanks that is working great! I didn't know if I needed to specify the table seen as it is specified at the start of the script, can I be really cheeky and ask how I would go by skipping the first row? Would this work?

 

$pdo->exec("IGNORE 1 LINES `$databasetable`");

Do you mean want to ignore the first line of the csv file? If so then No

 

You need add  IGNORE 1 LINES as part of the  LOAD DATA INFILE  directive like so

$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)." 
IGNORE 1 LINES");