[PHP] Mysql & php & Load Data Infiles
While I think this is primarily a mysql syntax problem I have asked on the mysql forum but not got a reply. I am trying using php to set up a system for loading a new data base using the Load Data Infile statement. I could not get it to work properly with php so I thought I would try some command line test. Does anyone know the loaction of a suitable php script that would handle a list of files and tables for loading into a database (preferably doing an initial dummy run using temporary tables with an error report). If not when I get this wretched syntax issue solved I guess I might have one available fairly soon. Here are my notes: This exercise was a test in preparation for a plan to use Load Data Infile for initallising a new database comprising over 80 tables. The database currently has no data. The first field is auto-increment. What am I doing wrong here? If I cannot do it using Load Data Infile what alternatives would you suggest? ___ Here is mysql command line dialogue on a win XP development machine: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 85 to server version: 5.0.13-rc-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use NewBuddies; Database changed mysql> load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' REPLACE into table lbcm_diet_types fields terminated by ',' ENCLOSED by '"'; ERROR 1265 (01000): Data truncated for column 'Help' at row 1 mysql> describe lbcm_diet_types; +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | Diet_Type_ID | int(11) | NO | PRI | NULL | auto_increment | | Diet_Type_Title | varchar(30) | NO | UNI | Enter Title | | | Diet_Type_Descr | text | NO | | | | | Help | int(11) | NO | | 1 | | +-+-+--+-+-++ 4 rows in set (0.01 sec) mysql> select * from lbcm_diet_types; Empty set (0.00 sec) mysql> ___ Here is the content of the first file created as a text file using editor macromedia: ___ "1","Unlimited","Anything goes","1" "2","Lacto-Vegetarian","Vegetarian consuming milk products, no meat, no fish","1" "3","Fish_Lacto-Vegetarian","Lacto Vegetarian plus fish","1" I then realized my obvious mistake two "'"s in the third field on line two. I removed "REPLACE" from the command and ran it again: mysql> load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by '"'; Query OK, 2 rows affected, 3 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 3 mysql> select * from lbcm_diet_types; +--+---++--+ | Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help | +--+---++--+ | 1 | Unlimited | Anything goes | 1 | | 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 | +--+---++--+ 2 rows in set (0.00 sec) /* OK we have progress BUT */ /*What happened to line 2? */ /* Why 0 for the help ID on line 3? */ /* So I amended the file to read: */ "4","Diabetic","See Diet Sheet","1" "5","Glutenfree","No wheat products or other glutenous grains","1" "6","Nut allergic","Avoid all nut products","1" /* and with the command:*/ mysql> load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by '"'; Query OK, 2 rows affected, 3 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 3 /* I got:*/ mysql> select * from lbcm_diet_types; +--+---++--+ | Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help | +--+---++--+ | 1 | Unlimited | Anything goes | 1 | | 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 | | 4 | Diabetic | See Diet Sheet | 1 | | 6 | Nut allergic | Avoid all nut products | 0 | +--+---++--+ 4 rows in set (0.00 sec) mysql> /* showing that the second line from this file is again being dropped and the last line gets 0 for help! - I must be missing something obvious but I am damned if I can see it /* So I realize it might be due to the windows CR/LF oddity therefore I try: */ mysql> load data local inf
Re: [PHP] Load Data Local Infile
Jason Wong wrote: On Thursday 02 September 2004 17:18, Harlequin wrote: I am using the following command and it appears to execute but does not actually load any data: LOAD DATA LOCAL INFILE 'public_html/CSV/act-export.csv' INTO TABLE MembersData FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; Is this new PHP 5 syntax? Any ideas what I might be missing...? Probably the MySQL list. You are behind the times jason, this list has been taken over by the dark forces of mysql -- Raditha Dissanayake. http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 128 KB | with progress bar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data Local Infile
On Thursday 02 September 2004 17:18, Harlequin wrote: > I am using the following command and it appears to execute but does not > actually load any data: > > LOAD DATA LOCAL INFILE 'public_html/CSV/act-export.csv' INTO TABLE > MembersData > FIELDS TERMINATED BY ',' > LINES TERMINATED BY '\n'; Is this new PHP 5 syntax? > Any ideas what I might be missing...? Probably the MySQL list. -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-general -- /* "Do you think there's a God?" "Well, SOMEbody's out to get me!" -- Calvin and Hobbs */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Load Data Local Infile
[snip] I am using the following command and it appears to execute but does not actually load any data: LOAD DATA LOCAL INFILE 'public_html/CSV/act-export.csv' INTO TABLE MembersData FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; Any ideas what I might be missing...? The file exists, and I must have the path right or would get an error but still no joy unfortunately... [/snip] Do you have the proper permissions to read the file? BTW, OT. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load Data Local Infile
I am using the following command and it appears to execute but does not actually load any data: LOAD DATA LOCAL INFILE 'public_html/CSV/act-export.csv' INTO TABLE MembersData FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; Any ideas what I might be missing...? The file exists, and I must have the path right or would get an error but still no joy unfortunately... -- - Michael Mason Arras People www.arraspeople.co.uk - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Load data and Insert
[snip] i need do two querys in one. First query is a load data and the second query is insert into. My idea is to concatenate with "and", but i'not know. Is it possible? [/snip] a. It is possible. http://catb.org/~esr/faqs/smart-questions.html 2. This is a PHP list, not a SQL list. III. HTH! INSERT INTO `table1` SELECT * FROM `table2` You can use conditions, the number of columns must match. http://dev.mysql.com/doc/mysql/en/INSERT.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load data and Insert
Hi! i need do two querys in one. First query is a load data and the second query is insert into. My idea is to concatenate with "and", but i'not know. Is it possible? Regards, Juan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] load data infile
[snip] I have a tab delimited file I am trying to load in my database using: LOAD DATA INFILE '/users/dbs/sites/phpmyadmin/gus/exptab.txt' INTO TABLE datable; But all it will load is the first record and I have about 2000 records. There are 20 columns of data in my 'exptab.txt' file I am loading and there are 20 fields in datable. [/snip] Have you, by any chance, consulted the MySQL list? http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] load data infile
I have a tab delimited file I am trying to load in my database using: LOAD DATA INFILE '/users/dbs/sites/phpmyadmin/gus/exptab.txt' INTO TABLE datable; But all it will load is the first record and I have about 2000 records. There are 20 columns of data in my 'exptab.txt' file I am loading and there are 20 fields in datable. Any ideas, if you could email me directly I would appreciate it, thanks, Doug -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Load Data
Hi Juan Pablo, LOAD DATE INFILE requires the FILE privelege, and the MySQL server process must have permissions to read the file in the named directory. Most times one or both of these requirements cannot be satisfied easily, particularly in a web environment. You can get around both permissions issues using LOAD DATA LOCAL INFILE, which sends the contents of the infile over the MySQL socket to the MySQL server. In this case your running application (ie: apache, assuming PHP is running as a module) must have read permissions on the file, which is much easier to control. Regards, James >-Original Message- >From: Juan Pablo Herrera [mailto:[EMAIL PROTECTED] >Sent: Thursday, June 10, 2004 4:15 PM >To: [EMAIL PROTECTED] >Subject: [PHP] Load Data > > >Hello! >I need a problem with mysql "Load Data". PHP say: "Access denied for user: >'[EMAIL PROTECTED]' (Using password: YES)", but my user and password is >correct.The sql query is: >$query_string2 = "LOAD DATA INFILE >'/usr/local/psa/home/vhosts/allaria.com.ar/httpdocs/XLS/precios' REPLACE >INTO TABLE precio FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES >TERMINATED BY '\n'"; >I used a vhost, what's a problem? > >Regards. >Juan Pablo > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load Data
Hello! I need a problem with mysql "Load Data". PHP say: "Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)", but my user and password is correct.The sql query is: $query_string2 = "LOAD DATA INFILE '/usr/local/psa/home/vhosts/allaria.com.ar/httpdocs/XLS/precios' REPLACE INTO TABLE precio FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"; I used a vhost, what's a problem? Regards. Juan Pablo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data infile
Juan Pablo Herrera wrote: [snip] I need do load data infile in mysql: $query_string2 = "LOAD DATA INFILE '/var/www/xls/test' REPLACE INTO TABLE `test` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n'";$query_db_string2 = mysql_query($query_string2); But not realize nothing with execution the script. [/snip] have you checked mysql_error() ? yes, You have an error in your SQL syntax near ''' at line 2 but i change '\"' and not work. The problem is PHP parses the string before it's passed to MySQL. Since you're using double quotes "ESCAPED BY '\\' LINES" is parsed to "ESCAPED BY '\' LINES" by PHP and then sent to MySQL, which causes an error. Two solutions: 'ESCAPED BY \'\\\' LINES' (use single quotes) or "ESCAPED BY '' LINES" -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data infile
> [snip] > I need do load data infile in mysql: > $query_string2 = "LOAD DATA INFILE '/var/www/xls/test' REPLACE INTO > TABLE > `test` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES > TERMINATED BY '\n'";$query_db_string2 = mysql_query($query_string2); > But not realize nothing with execution the script. > [/snip] > > have you checked mysql_error() ? yes, You have an error in your SQL syntax near ''' at line 2 but i change '\"' and not work. Thank you, Juan Pablo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data infile
[snip] I need do load data infile in mysql: $query_string2 = "LOAD DATA INFILE '/var/www/xls/test' REPLACE INTO TABLE `test` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n'";$query_db_string2 = mysql_query($query_string2); But not realize nothing with execution the script. [/snip] have you checked mysql_error() ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load Data infile
Hi! I need do load data infile in mysql: $query_string2 = "LOAD DATA INFILE '/var/www/xls/test' REPLACE INTO TABLE `test` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n'";$query_db_string2 = mysql_query($query_string2); But not realize nothing with execution the script. Thank you! Regards! Juan Pablo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data Infile
"Marco Tabini" <[EMAIL PROTECTED]> wrote in message: > I'm not sure how MySQL works under Windows, but you should either use > double backslashes (\\) instead of single backslashes or use forward > slashes (/) instead. MySQL is trying to escape your string. > > Cheers, > Marco Thanks Marco. C:\\TextFiles\\test.txt works now Happy new year to you and yours... TR -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load Data Infile
I'm not sure how MySQL works under Windows, but you should either use double backslashes (\\) instead of single backslashes or use forward slashes (/) instead. MySQL is trying to escape your string. Cheers, Marco -- php|architect - The Magazine for PHP Professionals The monthly magazine dedicated to the world of PHP programming Check us out on the web at http://www.phparch.com! --- Begin Message --- Off topic but I hope somebody can help. Using MS Win98 / mySQL. I'm trying to use LOAD DATA INFILE and coming up empty. My text file is named: test.txt .. and is stored in C:\TextFiles on the command line I type: .. mysql> LOAD DATA INFILE "C:\TextFiles\test.txt" INTO TABLE tony; ... I get: ERROR 1105 File c:\TextFile\ est.txt not found (Errcode:2) \\please note that the "t" in test is not there. Any help would be greatly appreciated. Happy holidays, Tony Ritter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- End Message --- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load Data Infile
Off topic but I hope somebody can help. Using MS Win98 / mySQL. I'm trying to use LOAD DATA INFILE and coming up empty. My text file is named: test.txt .. and is stored in C:\TextFiles on the command line I type: .. mysql> LOAD DATA INFILE "C:\TextFiles\test.txt" INTO TABLE tony; ... I get: ERROR 1105 File c:\TextFile\ est.txt not found (Errcode:2) \\please note that the "t" in test is not there. Any help would be greatly appreciated. Happy holidays, Tony Ritter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Load data infile
You may want to check permissions within your database. The web server user may not have permissions to perform this action. /dkm - Original Message - From: <[EMAIL PROTECTED]> To: "Philip Hallstrom" <[EMAIL PROTECTED]> Cc: "Peter J. Schoenster" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, May 14, 2002 6:53 PM Subject: [PHP] Load data infile > Hello, > > Is it possible to do the load data info mysql command via PHP > > > I get an access denied error from mysql when i try to do that. > > > > Thanks > > > Randy > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Load data infile
[snip] Is it possible to do the load data info mysql command via PHP I get an access denied error from mysql when i try to do that. [/snip] Are the permissions granted properly for the PHP user in the connection statement? Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Load data infile
Hello, Is it possible to do the load data info mysql command via PHP I get an access denied error from mysql when i try to do that. Thanks Randy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] LOAD DATA INFILE help
Hello everyone, I posted this message to the mysql mailing list and didn't get a response. I know a lot of people here are good with SQL. So, here's my e-mail: Hello, I've been struggling with this all night. Can someone give me a push in the right direction? I have a csv file named Sheet1.txt, here's an example of its contents: "P1002565", "P1002566", "P1002638", I want to put the contents of Sheet1.txt in a table named 'passcodes'. The passcodes table has 2 fields: 'id' and 'passcode'. I run this: LOAD DATA INFILE 'Sheet1.txt' INTO TABLE passcodes FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Nothing gets inserted into the 'passcode' field, but ID's are generated for every item in Sheet1.txt. Can anyone give me some help? Sorry for the sort of off-topic question! Thanks, Tyler Longren -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
