Page 1 of 1

LOAD LOCAL DATA INFILE Aaaahhhhhh!!!

Posted: Fri Apr 27, 2007 6:26 am
by BilboBoggins
Hey all,

SQL Version: MySQL 3.23.49
PHP Version: 4.3.11

Tearing my hair out with this 1. My PHP script was woking perfect until I moved to a different hosting server. When my page loads a PHP script imports the data into a MySQL table.

My code is:

mysql_query('LOAD DATA LOCAL INFILE "counciltotals.csv" INTO TABLE counciltotals FIELDS ENCLOSED BY "\"" TERMINATED BY "," LINES TERMINATED BY "\\r\\n" ;') or die('Error loading data file.<br>' . mysql_error());

The error message im now getting is:

File 'counciltotals.csv' not found (Errcode: 2)

Ive tried changing permissions and Ive also added local-infile=1 to the MySQL config file.

ANy Ideas would be great :-)

Posted: Fri Apr 27, 2007 7:36 am
by printf
Where is counciltotals.csv in relation to the MySQL install directory. If it's where the PHP script is running, then you need to add the full path to counciltotals.csv, because MySQL assumes that counciltotals.csv is located at /usr/local/mysql/counciltotals.csv on Linux or on Windows it would be drive_letter:\path_to_root_of_MySQL_install_directory\counciltotals.csv. So if counciltotals.csv is not there MySQL can not find it! So add the full path so MySQL knows where to find counciltotals.csv

Code: Select all

mysql_query('LOAD DATA LOCAL INFILE "' . $_SERVER['DOCUMENT_ROOT'] . '/counciltotals.csv" INTO TABLE counciltotals FIELDS ENCLOSED BY "\"" TERMINATED BY "," LINES TERMINATED BY "\\r\\n" ;') or die('Error loading data file.<br>' . mysql_error());

Posted: Fri Apr 27, 2007 11:55 am
by AKA Panama Jack
Your "counciltotals.csv" file has to be on the SAME server as the Mysql Server. Most hosting companies have MySql running on a separate server from PHP. So you cannot do what you are trying to do. This is one of the reasons most PHP/Mysql applications don't use the method you are trying to use to populate a database table.

If you have to use anything other than LOCALHOST to access the Mysql Database then you really can't use that query.