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! While you're at it, check out some resources Treehouse students have shared here.

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

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

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

1 Answer

Jeremy Hayden
Jeremy Hayden
1,740 Points

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.

Jeremy Hayden
Jeremy Hayden
1,740 Points

Actually, just realized I left out another obvious and easy solution for you. Instead of truncate, You can DELETE * FROM yourtable WHERE "identifyRowToBeSpared" != "someData".

You will have to double check the syntax, I typed this in a hurry.