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 <groans> /* So I realize it might be due to the windows CR/LF oddity therefore I try: */ mysql> load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by '"'L ines terminated by '\r\n'; Query OK, 1 row affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 2 Warnings: 0 /* This sounds more promising */ 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 | | 5 | Glutenfree | No wheat products or other glutenous gr ains | 1 | | 6 | Nut allergic | Avoid all nut products | 0 | +--------------+-----------------------+---------------------------------------- -----+------+ 5 rows in set (0.00 sec) /* Part of the problem solved - but what about those darned zeros? */ Any ideas? Thanks in advance david -- 40 yrs navigating and computing in blue waters. English Owner & Captain of British Registered 60' bluewater Ketch S/V Taurus. Currently in San Diego, CA. Sailing bound for Europe via Panama Canal after completing engineroom refit. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
