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

PHP: How can I give MySQL table a unique ID with this script?

Hey guys, I'm really stuck here I have this PHP 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";

?>

So as you can see that script is truncating my table with a CSV file. It replaces all the data currently in the table.

This data is used to create a listing page that includes vehicles that are in stock, each row contains the data for one vehicle. This script is ran once a day to replace vehicles that are no longer in stock.

I now want to give each of my SQL rows it's own page, I've been told I will need to use a 'NOT NULL AUTO_INCREMENT' attribute on an 'id' key.

However seen as my table truncates wont the ID removed each time my script is ran?

Could I add anything to my script to combat this problem?

Thanks

1 Answer

Your id field with not be removed when you TRUNCATE.

TRUNCATE TABLE removes all rows from the table and resets the AUTO_INCREMENT to zero.