FutureCake FutureCake - 3 years ago 239
PHP Question

Upload csv file to mysql database error in sql syntax

I have a piece of javascript that fetches a file and generates a name. This data gets put into a FormData object. This gets send by ajax to the server. See the javascript below:

function create_form_data(option_file_name){
var file = $('#'+option_file_name.id).prop('files')[0];
var form_data = new FormData();
form_data.append('file', file);
form_data.append('tblname', option_file_name.id.slice(12));
send_ajax_request(form_data);

}

function send_ajax_request(pdata){
$.ajax({
url: "set_points_data.php",
data: pdata,
contentType: false,
processData: false,
dataType: "text",
type: 'post',
success: function (data) {
console.log(data);
},
error: function(a,b,c){
console.log(a);
console.log(b);
console.log(c);
}
});
}


Now in the set_points_data.php i do the following:

include 'data.php';

if(isset($_FILES)){
$data = $_FILES["file"]["name"];
$db_name = mysqli_escape_string($conn, $_POST["tblname"]);
return_ajax(create_table($conn, $db_name, $data));
}else{
return_ajax("error");
}

function create_table($conn, $name, $data){
send_sql($conn, "DROP TABLE IF EXISTS domestic_data_strings.".$name."");
send_sql($conn, "CREATE TABLE $name (options varchar(255));");
send_sql($conn, "LOAD DATA LOCAL INFILE $data INTO TABLE $name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (options)");

return $name;

}


But I keep getting the following error :


Error: LOAD DATA LOCAL INFILE column_age.csv INTO TABLE age FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
' (options)

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 'column_age.csv INTO TABLE age FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' at line 1


I have no idea what is going wrong here :( I tried different
.csv
files, but that didnt make a difference. I checked if the table existed with the correct column what is also correct. Any help is greatly appreciated!

If more context is required please let me know!

Answer Source

I'm not sure but I think you have to surround the file name in your query with single quotes: send_sql($conn, "LOAD DATA LOCAL INFILE '" . $data . "' INTO TABLE $name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (options)");

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download