PHP & MySQL: Truncate a MySQL table but exclude first column

Question

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 :)

Answer 1 · 2014-10-07T01:18:52Z

October 7, 2014 1:18am

Truncate is basically a delete all command. In some databases it actually deletes the whole table and rebuilds it again from scratch.

If you do not want to delete everything, then delete groups of rows instead.

Also another option, would be to capture the first row of your table, save it in an array, then truncate the table. Then you could put that row back in from the array, then your data. This is a crazy round about approach, but it complies with the way you asked the question.

Perhaps you should also consider if the best approach is to completely wipe your tables each time. Is there a better way to capture your data from the original source? Could you send over only modified entries instead? Or compare the incoming file with your table and only change the differences?

It makes it hard to have sub tables and relationships if you are completely wiping each time.

Welcome to the Treehouse Community

Looking to learn something new?

nicholas maddren

nicholas maddren

PHP & MySQL: Truncate a MySQL table but exclude first column

1 Answer

Jeremy Hayden

Jeremy Hayden

Jeremy Hayden

Jeremy Hayden