homepage Welcome to WebmasterWorld Guest from 54.145.191.14
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Confusing syntax when loading file into MySQL DB with PHP
Native CLI and PHP Code with LOAD DATA INFILE
benni_203

5+ Year Member



 
Msg#: 77 posted 2:36 am on Oct 10, 2005 (gmt 0)

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

10+ Year Member



 
Msg#: 77 posted 12:39 pm on Oct 10, 2005 (gmt 0)

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

5+ Year Member



 
Msg#: 77 posted 1:33 pm on Oct 10, 2005 (gmt 0)

Valder,

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

Best Regards,
Benni

valder

10+ Year Member



 
Msg#: 77 posted 3:42 pm on Oct 10, 2005 (gmt 0)

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 :-)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved