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
Posted by nicholas maddren
nicholas maddren
12,793 Points

Code Question

I have this code here:

<?php $databasehost = "localhost"; $databasename = "database"; $databasetable = "import"; $databaseusername="username"; $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)." 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";

?>

Now as you can see it imports a CSV file into my database and it works fantastic although I do have some questions.

  1. I want the CSV to replace all data in the table. How do I do that it just simply adds up at the moment.

  2. How do I stop people from visiting the php file and refreshing it over again? For example if it's in my public_html anyone can visit the php file and refresh it.

  3. How would I go by skipping the first row of the CSV as it includes column titles?

Thanks if anyone could help with this it would be awesome :)!

5 Answers

thomascawthorn
thomascawthorn
22,986 Points
thomascawthorn
thomascawthorn
22,986 Points

Hey man!

To replace, make sure you use 

REPLACE INTO TABLE

You can find more information here: http://dev.mysql.com/doc/refman/5.0/en/replace.html

But essentially the REPLACE command will overwrite an entry where the primary keys match.

To stop people from refreshing the page over and over, you could set a session - If the user has run the function, you can set a sessions variable of 'visited' to true. 

<?php
session_start(); //make sure you don't miss this at the top the page!

//Inside function, when function successfully completes:
    $_SESSION['visited'] = true;

//then check before the function
if (!$_SESSION['visited']) {
    //do stuff
}
?>

As for the final point, you can use an IGNORE command. The example I found was here: http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table *See edit-2 of the selected answer

I'm sure you could work this into your code ;)

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Hope some of this helps!

p.s. Take a look at the markdown cheatsheet to display your code :-)

nicholas maddren
nicholas maddren
12,793 Points
nicholas maddren
nicholas maddren
12,793 Points

Thanks for the answer! I am unsure where I can insert the replace syntax I've tried replacing it with the LOAD DATA LOCAL INFILE syntax however I get an error :(

thomascawthorn
thomascawthorn
22,986 Points

No worries! :-)

This is what you have:

$affectedRows = $pdo->exec(" LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." 
                             INTO TABLE $databasetable 
                             FIELDS TERMINATED BY " . $pdo->quote($fieldseparator)." 
                             LINES TERMINATED BY ".$pdo->quote($lineseparator));

You should put REPLACE before 'INTO TABLE' on the second line

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

Hope this helps :)

nicholas maddren
nicholas maddren
12,793 Points
nicholas maddren
nicholas maddren
12,793 Points

Thanks so I have now added that code :

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

And it is doing exactly what it was. It just adds data onto the database.

Any idea why? Thanks

thomascawthorn
thomascawthorn
22,986 Points

Have you set a primary key on the table you're adding into? The REPLACE command will match by primary keys and replace the data for the row.

nicholas maddren
nicholas maddren
12,793 Points
nicholas maddren
nicholas maddren
12,793 Points

I have not yet set a primary key, I don't really know what one is so it looks like I will have to watch the database foundation to understand what one is. I have tried researching it and still don't totally understand what one is. Don't suppose you could explain a little for me? It's fine if you can't you have done so much more me so far ha I'll just watch the videos.

thomascawthorn
thomascawthorn
22,986 Points

haha! It's good revision for me man, no worries. At it's most basic - it's a unique id for a row in a database table. e.g. product_id, user_id etc.. So when you use REPLACE, mySQL will look at the primary keys on the existing table, compare against the incoming data, and it will update existing primary keys (and add new entries with a new primary key).

Everything is explained really well in those videos you mentioned, so I would suggest taking a look :-)

nicholas maddren
nicholas maddren
12,793 Points
nicholas maddren
nicholas maddren
12,793 Points

Okay thanks for all your help I'll take a look at them now :)