Advanced Search

Results 1 to 4 of 4

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

  1. #1
    Join Date
    Aug 2011
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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:

    PHP 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).")"

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,627
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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 (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.
    Quote Originally Posted by php.net (official PHP website)
    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 or PDO_MySQL extension should be used.
    See also the MySQL API Overview for further help while choosing a MySQL API.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  3. #3
    Join Date
    Aug 2011
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default

    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:
    Code:
    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;
    	 }
        }
    Last edited by Beverleyh; 02-09-2014 at 04:15 PM. Reason: Formatting

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,627
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by antonyf View Post
    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].
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

Similar Threads

  1. using LOAD DATA LOCAL INFILE
    By a6april in forum PHP
    Replies: 3
    Last Post: 02-13-2013, 06:15 PM
  2. How can i change entries_url to load from local file
    By CyberPath in forum JavaScript
    Replies: 0
    Last Post: 07-27-2011, 03:11 PM
  3. using LOAD DATA INFILE
    By fastsol1 in forum PHP
    Replies: 1
    Last Post: 08-27-2010, 03:12 AM
  4. Replies: 5
    Last Post: 07-19-2010, 10:04 AM
  5. Remove line breaks from text field
    By kitten2781 in forum JavaScript
    Replies: 3
    Last Post: 03-05-2007, 06:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •