using LOAD DATA LOCAL INFILE and updateing each userid field of each line

[antonyf]

Hi.
Im using a function to import users contacts file to db. I make use of "LOAD DATA LOCAL INFILE" to populate the data base.. The app is a frontend app and the .csv file is a users own .csv file so it does not contain a site user_id.

I also use a data mapping system to map the columns to the tables in the DB, before the save function. My dilemma is:

i am trying to also update the users is with every line from the .csv that is added to the db using the "LOAD DATA LOCAL INFILE" function..

here is my code:



// this code works to flawlessly, but i dont know where to add an insert into user_id or set the user id of each new line added..
$sql = "LOAD DATA LOCAL INFILE '".@mysql_escape_string($this->file_name).
"' IGNORE INTO TABLE `".$this->table_name.
"` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
"' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
"' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
"' LINES TERMINATED BY '". $this->line_separate_char .
"' ".
($this->use_csv_header ? " IGNORE 1 LINES " : "")
."(".implode(",", $fields).")";

[traq]

To answer your specific question, the simplest way would be to turn this into a stored procedure which you could then provide with the desired user id. (Actually, the simplest solution would be to require the user to include their user id in the csv.)

However, I wouldn't recommend either of these solutions. I hope you can appreciate the irony in escaping the filename of a user-provided file which you are dumping into your database: it's like requiring someone to wipe their feet before you let them into your house to dump garbage in your living room.

(In addition, using the error suppression operator (@) only guarantees that if something goes wrong, you will never know about it. It's intended to allow you to implement your own error handling, not to ignore errors.)

The data in the file needs to be validated and sanitized before being allowed into the database. I would recommend converting the csv file to an array (http://php.net/manual/en/function.fgetcsv.php) (where you could then add a "user_id" index), re-saving the file, and then loading it.


--------------------------------------------------
# If at all possible, you should avoid using the mysql_* functions. #
ext/mysql has been outdated, and recommended against, since 2004. Existing code should be updated to avoid performance and security problems.


Warning
This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future.
Instead, either the MySQLi (http://php.net/mysqli) or PDO_MySQL (http://php.net/PDO) extension should be used.
See also the MySQL API Overview (http://php.net/mysqlinfo.api.choosing) for further help while choosing a MySQL API.

[antonyf]

Hi traq.
Sorry for the long response time. I did manage to fix this issue the way you described above. here is the code is used:



if($continue == 2)
{
$seperators = $_POST['field_separate_char'];
$our_name = $tempdir.'/'.basename($_POST['fnames']);
$new_file = $tempdir.'/'.basename($User->id.'.csv');
if(file_exists($new_file)){
unlink($new_file);
}
if(!file_exists($our_name)){
echo 'The file did not upload. Please try the process again.';
exit;
} else {
if($seperators == ''){
$separat = CSV::try_separators( CSV::get_line($our_name) );
if( empty($separat ) ) {
exit( 'Cannot autodetect the separator' );
}
$seperators = $separat;
}
$nlines = explode("\r\n",file_get_contents($our_name));
$arr_head = CSV::get_header_fields( $db, $our_name, 'utf8', $seperators, '"', '\\' );;
$num = count($arr_head);
$nwFile = fopen($new_file, 'w');
$newarray ='';
foreach (array_values($nlines) as $value){
if($value != '') {
$nnmu = explode($seperators, $value);
$totnum = count($nnmu);
if($totnum == $num){
$newarray .= $value.','.$User->id."\n";
}
}
}
fwrite($nwFile,$newarray);
fclose($nwFile);
unlink($our_name);

$_SESSION['csvdata']['file_name'] = $new_file;
$_SESSION['csvdata']['use_csv_header'] = $_POST['use_csv_header'];
$_SESSION['csvdata']['field_separate_char'] = $seperators;
$_SESSION['csvdata']['field_enclose_char'] = '"';
$_SESSION['csvdata']['field_escape_char'] = '\\';
$_SESSION['csvdata']['encoding'] = 'utf8';
$_SESSION['csvdata']['table'] = 'jos_mycont';
$_SESSION['csvdata']['thisuid'] = $thisJRUser->id;
echo '1';
exit;
}
}

[traq]

I did manage to fix this issue…
Good to hear.

If your question has been answered, please mark your thread "resolved":
On your original post (post #1), click [edit], then click [go advanced]. In the "thread prefix" box, select "Resolved". Click [save changes].