LOAD LOCAL DATA INFILE Aaaahhhhhh!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BilboBoggins
Forum Newbie
Posts: 1
Joined: Fri Apr 27, 2007 6:23 am

LOAD LOCAL DATA INFILE Aaaahhhhhh!!!

Post 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 :-)
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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());
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
Post Reply