2

I have the following query...how would I go about troubleshooting why it is not loading any data (my connection to my db is fine).

$filename = "/data/101Phones-Product_Catalog_TXT.txt";

mysql_query('load data infile "'.$filename.'" into table CJProducts fields terminated by "\t" lines terminated by "\n" (PROGRAMNAME, PROGRAMNAME, CATALOGNAME, LASTUPDATED, NAME, KEYWORDS, DESCRIPTION, SKU, MANUFACTURER, MANUFACTURERID, UPC, ISBN, CURRENCY, SALEPRICE, PRICE, RETAILPRICE, FROMPRICE, BUYURL, IMPRESSIONURL, IMAGEURL, ADVERTISERCATEGORY, THIRDPARTYID, THIRDPARTYCATEGORY, AUTHOR, ARTIST, TITLE, PUBLISHER, LABEL, FORMAT, SPECIAL, GIFT, PROMOTIONALTEXT, STARTDATE, ENDDATE, OFFLINE, ONLINE, INSTOCK, CONDITION, WARRANTY, STANDARDSHIPPINGCOST)');

http://billsprice.com/1_800_FLORALS-Product_Catalog_1.txt

EDIT:

 mysql> load data infile "/data/101Phones-Product_Catalog_TXT.txt" into table CJProducts fields terminated by "\t" lines terminated by "\n" (`PROGRAMNAME`, `PROGRAMURL`, `CATALOGNAME`, `LASTUPDATED`, `NAME`, `KEYWORDS`, `DESCRIPTION`, `SKU`, `MANUFACTURER`, `MANUFACTURERID`, `UPC`, `ISBN`, `CURRENCY`, `SALEPRICE`, `PRICE`, `RETAILPRICE`, `FROMPRICE`, `BUYURL`, `IMPRESSIONURL`, `IMAGEUR`, `ADVERTISERCATEGORY`, `THIRDPARTYID`, `THIRDPARTYCATEGORY`, `AUTHOR`, `ARTIST`, `TITLE`, `PUBLISHER`, `LABEL`, `FORMAT`, `SPECIAL`, `GIFT`, `PROMOTIONALTEXT`, `STARTDATE`, `ENDDATE`, `OFFLINE`, `ONLINE`, `INSTOCK`, `CONDITION`, `WARRANTY`, `STANDARDSHIPPINGCOST` SHOW ERRORS
user1179295
  • 706
  • 3
  • 10
  • 21

1 Answers1

1

Use SHOW ERRORS and SHOW WARNINGS to get explicit messages.

Alternatively, try to execute this request in the mysql console to get more information.

Try to check this answer too: Can I detect and handle MySQL Warnings with PHP?.

Also backquote your column names as, for example, CONDITION is a reserved keyword. Modify your query as below:

mysql_query('load data infile "'.$filename.'" into table CJProducts fields terminated by "\t" lines terminated by "\n" (`PROGRAMNAME`, `PROGRAMNAME`, `CATALOGNAME`, `LASTUPDATED`, `NAME`, `KEYWORDS`, `DESCRIPTION`, `SKU`, `MANUFACTURER`, `MANUFACTURERID`, `UPC`, `ISBN`, `CURRENCY`, `SALEPRICE`, `PRICE`, `RETAILPRICE`, `FROMPRICE`, `BUYURL`, `IMPRESSIONURL`, `IMAGEUR`, `ADVERTISERCATEGORY`, `THIRDPARTYID`, `THIRDPARTYCATEGORY`, `AUTHOR`, `ARTIST`, `TITLE`, `PUBLISHER`, `LABEL`, `FORMAT`, `SPECIAL`, `GIFT`, `PROMOTIONALTEXT`, `STARTDATE`, `ENDDATE`, `OFFLINE`, `ONLINE`, `INSTOCK`, `CONDITION`, `WARRANTY`, `STANDARDSHIPPINGCOST`)');

Finally, you have written PROGRAMNAME twice. Is that normal?

Community
  • 1
  • 1
Julien Bourdon
  • 1,713
  • 17
  • 28
  • ok i added than at the end of mysql statement, nothing is returned. Thoughts? – user1179295 Feb 27 '12 at 03:46
  • I edited my answer and linked to another answer. By the way, could you edit your question to show a sample of your data file? – Julien Bourdon Feb 27 '12 at 03:50
  • I get this back #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONDITION, WARRANTY, STANDARDSHIPPINGCOST) SHOW ERRORS and SHOW WARNINGS'' at line 1 I will add a sample file too. – user1179295 Feb 27 '12 at 03:51
  • You need to backquote reserved words. Modified my answer. – Julien Bourdon Feb 27 '12 at 04:38
  • well i tried it on phpmyadmin...but the file isnt on that server. The database is on its own server (I am using amazons RDS)...so i think there is problems with the directory of the file and sql figuring out where that is at. Do you see what i mean? – user1179295 Feb 27 '12 at 07:25
  • Try to connect to the database with mysql CLI. – Julien Bourdon Feb 27 '12 at 07:28
  • check out the above...i tried it through shell and it goes through but does load the file and even with show warnings it doesnt show anything – user1179295 Feb 27 '12 at 13:54