Truncate a MySQL table but exclude first column

[Nickmadd]

Hey guys, I'm a little confused to how I can do this.

 

I am basically wanting to give my first column a 'NOT NULL AUTO_INCREMENT' and give each row it's own 'id'. The issue I am having is that the script I am using truncates the whole SQL table with a CSV file that is cron'd daily to update data.

 

I am currently using this script:

<?php


$databasehost = "localhost";
$databasename = "";
$databasetable = "";
$databaseusername="";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/csv.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 `$databasetable`");

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

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

?>

Is it possible to amend this script to ignore my first column and truncate all of the data in the table apart from the first column?

 

I could then give all of the rows in the first column their own ID's any idea how I could do this?

 

I am still very nooby so please go easy on me

[requinix]

If you're going to overwrite the IDs then it sounds like you shouldn't be using auto_increment in the first place...

 

Truncating is removing all the data in the table. It doesn't make sense to truncate a table and leave a column behind. Do you mean to fill all the other columns with NULL? Why bother keeping the IDs when you overwrite them (in some other code, I assume) later?

[Nickmadd]

If you're going to overwrite the IDs then it sounds like you shouldn't be using auto_increment in the first place...

 

Truncating is removing all the data in the table. It doesn't make sense to truncate a table and leave a column behind. Do you mean to fill all the other columns with NULL? Why bother keeping the IDs when you overwrite them (in some other code, I assume) later?

 

I only need simple ID's for each row such as 1, 2, 3 etc.

 

The reason I am doing this is because I will be giving each one of the rows in my table it's own page. To do this I need to give them there own ID's, thus needing a column that has an ID for each row, I cant add the ID's to the CSV file as it is from a third party.

[requinix]

My strategy with imports is to import data into a table dedicated to importing. Nothing else but a database copy of the (in this case) CSV file.

When that's done you can run whatever queries you need to get the imported data into its rightful place. I think you should have a TRUNCATE TABLE followed by an INSERT...SELECT.