CODEWITHSUNDEEP
phpmysql
IlludiumPu36
IlludiumPu36

Reputation: 4304

LOAD DATA INFILE - FIELDS TERMINATED error

I have the following load data infile query:

LOAD DATA INFILE $csv INTO TABLE safety_quiz FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (first_name, last_name, person_id, quiz_result)

This produces the following error:

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 '../import/import_20_01_2014.csv INTO TABLE safety_quiz FIELDS TERMINATED BY ',' ' at line 1

the csv format is:

Bob,Smith,123456789,100
Fred,Jones,987654321,100

the table structure is:

CREATE TABLE `safety_quiz` (
  `safety_quiz_pk` int(8) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
  `person_id` varchar(9) NOT NULL,
  `quiz_result` varchar(10) NOT NULL,
  PRIMARY KEY (`safety_quiz_pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I have looked at other stackoverflow questions on the same issue, but I can't see where the error is...

UPDATE

Full code:

if(isset($_POST['submit'])){

if(!empty($_FILES["file"]["tmp_name"])){

$type = explode(".",$_FILES['file']['name']);

if(strtolower(end($type)) == 'csv'){

$path = '../import/';   
$file_name = 'import_' . date('d_m_Y') . '.csv';
move_uploaded_file($_FILES["file"]["tmp_name"], $path . $file_name);

} else {
    $message = 'Only csv files can be uploaded.';   
}
} else {
    $message = 'You need to attach a csv file.';    

}

$csv = $path . $file_name;

$query = "LOAD DATA INFILE '$csv' INTO TABLE safety_quiz FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (first_name, last_name, person_id, quiz_result)";
$result = mysql_query($query, $connection) or die(mysql_error());
}

WORKING CODE

Looks like the path to the csv needs to be absolute (don't know why...), but this works:

$csv = '/var/www/html/labs/import/' . $file_name;

$query = "LOAD DATA INFILE '$csv' INTO TABLE safety_quiz FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (first_name, last_name, person_id, quiz_result)";
$result = mysql_query($query, $connection) or die(mysql_error());
}

Upvotes: 2

Views: 1537

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

The following example was taken from http://dev.mysql.com/doc/refman/5.5/en/load-data.html located near the bottom of the page.

"LOAD DATA INFILE '$myFile'" . " INTO TABLE test FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'

and it shows the file's variable encapsulated in quotes.

Try the following:

LOAD DATA INFILE '$csv' INTO TABLE safety_quiz FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (first_name, last_name, person_id, quiz_result)

And as Ohgodwhy stated in a comment is that the $csv variable isn't encapsulated correctly and therefore is interpreted literally.

Or encapsulated in single and double quotes with dots:

LOAD DATA INFILE '".$csv."'

borrowed from this answer on SO

Upvotes: 2

Related Questions