Welcome to WebmasterWorld Guest from 54.226.27.104

Forum Moderators: open

Message Too Old, No Replies

Confusing syntax when loading file into MySQL DB with PHP

Native CLI and PHP Code with LOAD DATA INFILE

     

benni_203

2:36 am on Oct 10, 2005 (gmt 0)

10+ Year Member



I am currently trying to learn MySQL. I have a local Apache/PHP/MySQL installation on my WinXP machine. My first exercise was to write a script, which loads a comma separated file into a MySQL database. Everything works great, there is just a confusing syntax rule which I have problems to understand:

This is the command which is executed fine on the MySQL CLI:

LOAD DATA LOCAL INFILE 'c:\\tmp\\import.txt' 
INTO TABLE scwtble
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

I accept the two \\ in the filename and path, took me a while to figure it out though. So far so good. The next step was to incorporate the same statement into a php-script and my first thought was (which did not work)

 $sql= "LOAD DATA LOCAL INFILE 'c:\\tmp\\import.txt' 
INTO TABLE scwtble
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'";

This is not working. I tried various combinations for hours. Finally, I used phpmyadmin and created the php-code from there and the correct php code is according to that:

 $sql= 'LOAD DATA LOCAL INFILE \'c:\\\\tmp\\\\import.txt\' '
. ' INTO TABLE scwtble'
. ' FIELDS TERMINATED BY \',\' '
. ' LINES TERMINATED BY \'\\n\'';

This code works. I am frustrated that I could not figure it out by myself ;-)

1. Does anyone know why we now need 4 (!) Back-Slashes \\\\?
2. Why is the single quotation mark working and the double not? My version with mixing up double and single made more sense to me.
3. Does splitting up the command in multiple chunks make a difference?
4. Does anyone know an easy way to output the native php error codes on a html-page? I was always doing trial and error without knowing the proper error-codes.

valder

12:39 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



1. backslash is the escape character for both mysql and php. So if the path is 'c:\temp\whatever' MySQL will escape the t and the w, but none of them have special values anyway, so nothing fancy will happen except you'll lose the intended backslash. So to get the backslash character in MySQL you'll need to escape it - thus, 2 backslashes.

The same happens in php, that's why you need 4.

2. you should be able to do it with double quotes, but it's often better to do it with single quotes, because text in double quotes will be parsed, so if there are any special values or variables inside they'll transform. It depends on the job, and in any case you need to know what you're doing. In your query, you're using \n, which will transform into a newline if you're using double quotes. Not sure if that matters to MySQL. Just escape the backslash like the working example did and you'll be fine.

3. no, except for making it easier for people to read

4. if you mean the mysql error codes, you can use the php functions mysql_errno() and mysql_error(), like this:

if (!mysql_query($query, $db_resource)) {
echo mysql_errno() . ' : ' . mysql_error();
}

That will display the mysql error number and message from the last query if it failed.

benni_203

1:33 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



Valder,

Thank You for this great answer. That makes 100% sense.

Best Regards,
Benni

valder

3:42 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



Re-reading my post, I see there's one mistake..

So if the path is 'c:\temp\whatever' MySQL will escape the t and the w, but none of them have special values anyway, so nothing fancy will happen except you'll lose the intended backslash.

That's not exactly true..

\t means tab, so the path would be translated by MySQL as 'c:<tab>empwhatever'

Doesn't really matter in this case, but it might be worth noting anyway if you're learning all this coding stuff :-)

 

Featured Threads

Hot Threads This Week

Hot Threads This Month