Loading CSV into MySQL table with PHP

Question

I am trying to import a CSV into a MySQL table with a PHP script. This SQL command successfully imports the CSV file into the SQL table:

mysql> LOAD DATA LOCAL INFILE 'property_re_1.csv' 
-> REPLACE INTO TABLE `markers`
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
-> LINES TERMINATED BY '\n' IGNORE 1 LINES;
Query OK, 315 rows affected (0.01 sec)
Records: 315  Deleted: 0  Skipped: 0  Warnings: 0

Now I want to convert this SQL command into a PHP script. Here's my attempt at writing the PHP script:

<?PHP
$dbhost = 'localhost';
$dbuser = 'myusername';
$dbpasswd = 'mypassword';
$db = "db_markers";

$dbh = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Unable to connect to SQL server");
$my_db = @mysql_select_db($db, $dbh) or die("Unable to select database");

$file = $_SERVER['DOCUMENT_ROOT']."/path/property_re_1.csv";

$result=mysql_query("LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE `markers` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES");   
?>

This script results in an error:

HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request.

The errors log reads:

[18-Dec-2012 12:37:38] PHP Parse error: syntax error, unexpected    T_CONSTANT_ENCAPSED_STRING in /path/testconnect.php on line 13

The $Result variable is called on line 13, so the error must be therein.

I'm not sure what the cause of this error is. Any help is greatly appreciated!

Yes, here is the error from the log: [18-Dec-2012 12:37:38] PHP Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /path/testconnect.php on line 13. The $result variable is on line 13. — AME, Dec 18 '12 at 19:40
@dualed: also `\\` and `\n` is wrong ;) - @Ame: Please see http://php.net/string and double-quoted strings. you can not copy all characters one-to-one into the string, some need special treatment. — hakre, Dec 18 '12 at 19:45
@hakre I'm shocked you actually read through that before seeing the error ;) I don't see where `\n` is wrong though; great answer down there by the way — dualed, Dec 18 '12 at 19:50
@dualed: A good IDE underlines you the parts of the script, so it's merely a matter of a quick copy and paste. Checkout PHPStorm, it does that: http://blog.jetbrains.com/webide/2012/12/phpstorm-6-eap-build-123-237/ (30-day time-limited license) — hakre, Dec 18 '12 at 19:55
$result=mysql_query("LOAD DATA LOCAL INFILE ". $file ." REPLACE INTO TABLE markers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES"); — AME, Dec 18 '12 at 20:02
You should not use **mysql** functions. They are obsolete and have been removed from the latest versions of PHP. Instead use **mysqli** or **PDO**. — ryantxr, Feb 05 '18 at 22:08

score 3 · Answer 1 · edited May 23 '17 at 10:27

The cause of the error is the request you've send to the webserver. The webserver tries to fulfill the request and executes scripts to do that, in your case, the PHP script.

The PHP script now fails. The webserver only knows it failed, but as the webserver does not know anything specific, it will only give back the so called Internal Server Error (the error happened internally), code 500. The exact error information is hidden because the error was not expected and no internal information should be leaked to the outside world.

From the message alone, nobody can say what happened. You need to look into the error log of your webserver and check what the internal reporting was.

In your case I would assume that your PHP script has a fatal error. You can also enable PHP error display and logging, see PHP Does Not Display Error Messages.

When you do that, you might see more error messages. Common happening error messages are explained in our error reference: Reference - What does this error mean in PHP?.

If I should place a guess, I'd say you're seeing this because your PHP file does not parse. Most likely the following error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE

Happy trouble-shooting!

Loading CSV into MySQL table with PHP

1 Answers1

Linked