LOAD DATA LOCAL INFILE from PHP

A simple demonstration of escape characters gone wild… Well not really, referring to the PHP manual which states:

To specify a literal single quote, escape it with a backslash (\).

To specify a literal backslash, double it (\\).

With that in mind, the following is the content of the example .csv file we’ll be importing to the MySQL server:

ID, Description, Price
10,"Vostro 230 Mini Tower",349
15,"Vostro 460 Mini Tower",639
20,"Vostro 3500",509
25,"PowerEdge T610",1049

The part of the PHP code that deals with the import is as follows:

<?php
$target_path ='/absolute path to .csv'; 

$sql = 
  ' LOAD DATA LOCAL INFILE \''.$target_path.'\' REPLACE INTO TABLE tbl_name'
. ' FIELDS TERMINATED BY \',\''
. ' ENCLOSED BY \'"\''
. ' ESCAPED BY \'\\\\\''
. ' LINES TERMINATED BY \'\\n\''; 
mysql_query($sql) or die(mysql_error());
?>

When executed, this will be the resulting query:

LOAD DATA LOCAL INFILE '/absolute path to .csv' REPLACE INTO TABLE tbl_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\n'

So, it makes sense after all.

Thank you for reading!
Feel free to waste more time by subscribing to my RSS feed or check out the human-readable sitemap for more content.

Related posts