Advanced Search

Results 1 to 8 of 8

Thread: Remove lines from a csv if the preg_match email exists in db..

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

    Default Remove lines from a csv if the preg_match email exists in db..

    Hi.
    I have a code that imports contacts from a custom csv file. The layout of the files are never the same and determined by the user. IE: the email column may differ in position.
    With my function, i would like to find the email column, or email of every line and then in a foreach or while loop see if it exists in the users contacts table, if it does then exlude the line from being writen into the new .csv file.

    here is my code:
    Code:
    $seperators = $_POST['field_separate_char'];
    $our_name = $tempdir.'/'.basename($_POST['fnames']);
    $new_file = $tempdir.'/'.basename($User->id.'.csv');
        $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);
    
    // here i would like to find a the email in every line and then run a query to the db, if the email exists exclude the line from the $newarray below
      $totnum = count($nnmu);
      if($totnum == $num){ // here we seperate the uneven lines from the csv file, causes issues with alignment of data to columns
       $newarray .= $value.','.$User->id."\n";  // here we attach a new or multiple columns to the csv line..
       } 
       }
    }
    fwrite($nwFile,$newarray);
    fclose($nwFile);
    unlink($our_name);
    Last edited by Beverleyh; 02-09-2014 at 04:16 PM. Reason: Formatting

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

    Default

    Update:

    I did more research and although i cannot find an example for myself i have pieced together some stuff might be better then a preg_match.. The below code is not working though, any help..

    Code:
       $newar .= $value.','.$User->id."\n"; // is just a comma seperated string..
       $match = mysql_query("SELECT work_email, email as mail FROM contacts WHERE userid = '".(int)$User->id."' AND mail IN ('".implode("','", $newar)."'), $contLink");
     if(count($match) > 0){
     $newarray .= '';
     } else {
     $newarray .= $newar;
     }
    
    fwrite($nwFile,$newarray);
    fclose($nwFile);
    unlink($our_name);

  3. #3
    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
    Hi.
    I have a code that imports contacts from a custom csv file. The layout of the files are never the same and determined by the user. IE: the email column may differ in position.
    With my function, i would like to find the email column, or email of every line and then in a foreach or while loop see if it exists in the users contacts table, if it does then exlude the line from being writen into the new .csv file.
    Why not require everyone to use a standardized format (or even one of a few standardized formats)?

    Do you at least require a header line?

    I could not offer much specific advice without seeing the code you are using (the CSV class is not a part of the php core), but here's a few comments on the code you posted:
    PHP Code:
    <?php

    $seperators 
    $_POST['field_separate_char'];

    # is this an uploaded file?
    # the path to an uploaded file is available in the $_FILES superglobal. why are you trying to parse it here?
    $our_name $tempdir.'/'.basename($_POST['fnames']);

    # why basename()? does your $User->id contain directory separators?
    $new_file $tempdir.'/'.basename($User->id.'.csv');

        
    # there is a built-in php function for reading a file into an array of lines:
        # http://php.net/file
        # if the files are large, you can save a LOT of memory by reading only one line at a time:
        # http://php.net/fgets
        
    $nlines explode("\r\n",file_get_contents($our_name));
        
    # also, here, you're splitting lines on "\r\n", but further down, you use "\n" as an EOL marker.
        # one or the other or both of these will eventually fail when given a file with an unexpected line ending character.

            # if this method gets the names of the field headers,
            # then I would expect the class to also provide a way to select a specific header field...?
            # this would solve your problem.
        
    $arr_head CSV::get_header_fields$db$our_name'utf8'$seperators'"''\\' );;
            
    # also, you have an extra semicolon at the end of that line

        
    $num count($arr_head);
        
    $nwFile fopen($new_file'w');

            
    # why is your "newarray" a string?
        
    $newarray ='';

    # this is redundant: foreach operates on the array values by default anyway
    foreach (array_values($nlines) as $value){

    # instead of checking if the value is empty, wouldn't it make more sense to check if the $separator is present?
    if($value != '') {
      
    $nnmu explode($seperators$value);

    // here i would like to find a the email in every line and then run a query to the db, if the email exists exclude the line from the $newarray below
    # as I pointed out above, check whether your CSV class already has a method that finds the email field.

      
    $totnum count($nnmu);
      if(
    $totnum == $num){ // here we seperate the uneven lines from the csv file, causes issues with alignment of data to columns

       # writing lines to the file manually is error-prone.  I mentioned in your other thread, php has built-in functions for using csv-formatted files:
       # http://php.net/fgetcsv
       # http://php.net/fputcsv
       # I would assume the CSV class you're using would have similar methods.
       
    $newarray .= $value.','.$User->id."\n";  // here we attach a new or multiple columns to the csv line..
       

       }
    }
    fwrite($nwFile,$newarray);
    fclose($nwFile);
    unlink($our_name);
    Last edited by traq; 02-09-2014 at 05:38 PM.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

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

    Default

    Hi traq.
    Thanks for the reply. This csv uploader allows the user to upload any csv file, with any arrangement of columns. Then the very basic csv class will display the uploaded csv and database headers in a new html table. The user can then map name and email and phone etc to the db columns.. Then the info is saved to the db.. The function above allows me to put the users id into the newly uploaded users csv file... and then create a new csv file named after the users id... This is then parsed into the database.. The problem is it uses a "LOAD DATA LOCAL INFILE" function to load the db.. and so duplicates are added. I am trying to remove the duplicates from the uploaded file, before it is parsed to the csv class file and entered into the db..

    Code:
    $seperators = $_POST['field_separate_char'];
    
    # is this an uploaded file?
    # the path to an uploaded file is available in the $_FILES superglobal. why are you trying to parse it here?
    $our_name = $tempdir.'/'.basename($_POST['fnames']);// is the uploaded file
    
    # why basename()? does your $User->id contain directory separators?
    $new_file = $tempdir.'/'.basename($User->id.'.csv');// no seperators but this works
    
        # there is a built-in php function for reading a file into an array of lines:
        # http://php.net/file
        # if the files are large, you can save a LOT of memory by reading only one line at a time:
        # http://php.net/fgets
        $nlines = explode("\r\n",file_get_contents($our_name));
        # also, here, you're splitting lines on "\r\n", but further down, you use "\n" as an EOL marker.
        # one or the other or both of these will eventually fail when given a file with an unexpected line ending character.
    
            # if this method gets the names of the field headers,
            # then I would expect the class to also provide a way to select a specific header field...? // no way to get a header field, i would have to find the @ to determin the email..
    
    
            # this would solve your problem.
        $arr_head = CSV::get_header_fields( $db, $our_name, 'utf8', $seperators, '"', '\\' );
            # also, you have an extra semicolon at the end of that line
    
        $num = count($arr_head);
        $nwFile = fopen($new_file, 'w');
    
            # why is your "newarray" a string? // it is a string because i just added the user id to the end and then rewrote it to a new csv file to later parse to the csv class file to handle the db input
        $newarray ='';
    
    # this is redundant: foreach operates on the array values by default anyway: it doesnt work other wise to give me the value only in a string format
    foreach (array_values($nlines) as $value){
    
    # instead of checking if the value is empty, wouldn't it make more sense to check if the $separator is present? // ? this works too
    if($value != '') {
      $nnmu = explode($seperators, $value);
    
    // here i would like to find a the email in every line and then run a query to the db, if the email exists exclude the line from the $newarray below
    # as I pointed out above, check whether your CSV class already has a method that finds the email field. i dont have that function in the db :-(
    
      $totnum = count($nnmu);
      if($totnum == $num){ // here we seperate the uneven lines from the csv file, causes issues with alignment of data to columns
    
       # writing lines to the file manually is error-prone.  I mentioned in your other thread, php has built-in functions for using csv-formatted files:
       # http://php.net/fgetcsv
       # http://php.net/fputcsv
       # I would assume the CSV class you're using would have similar methods.
       $newarray .= $value.','.$User->id."\n";  // here we attach a new or multiple columns to the csv line..
       } 
       }
    }
    fwrite($nwFile,$newarray);
    fclose($nwFile);
    unlink($our_name);
    I would imagine to add a query somewhere to find emails in the imploded array.. Can you check my function above? This entire function is working for me accept the duplicates in the db.. thanks for the good tips otherwise i did fix some now..

  5. #5
    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
    Hi traq.
    Thanks for the reply. This csv uploader allows the user to upload any csv file, with any arrangement of columns. Then the very basic csv class will display the uploaded csv and database headers in a new html table.
    Yes, I assumed as much. If you want help with how to use it, you will need to post the code, since this is a "userland" class and, therefore, we have no way of knowing where it came from or how it works.

    Quote Originally Posted by antonyf View Post
    Then the info is saved to the db … The problem is it uses a "LOAD DATA LOCAL INFILE" function to load the db.. and so duplicates are added.
    This is not a PHP problem: it is better dealt with by the database. If your email column should have no duplicates, then define it as a UNIQUE column so the database wouldn't allow duplicate entries. If you then want duplicate entries to be ignored, use the IGNORE option when you load the file, so such rows will be skipped:
    Code:
    LOAD DATA LOCAL INFILE 'file.name' IGNORE …
      -- or REPLACE instead, depending on what you want to happen
    Last edited by traq; 02-09-2014 at 07:13 PM.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

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

    Default

    cool traq..
    the problem is i don't want the user to have duplicate entries, another user can have the same contact with the same email.. so the unique will not work... I'm looking for a select from db where email in ($value) or work_email in ($value), but its not working for me..

    Code:
    // here value is a comma separated string
     $exists=mysql_query("SELECT * FROM contacts WHERE 'email' IN ('".$value."') OR 'work_email' IN ('".$value."')");
      if(mysql_num_rows($exists) == 0){
    //it doesnt exist add the line: 
    $newvalue = $value.','.$userid"\n";
       } else {
    $newvalue = '';
    }
    // but this is not working..

  7. #7
    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
    cool traq..
    the problem is i don't want the user to have duplicate entries, another user can have the same contact with the same email.. so the unique will not work...
    So, each email must be unique per user, correct? Make the combination unique:
    Code:
    CREATE TABLE contacts(
        user_id INT         NOT NULL,
        email   VARCHAR(50) NOT NULL,
        -- your other columns
        UNIQUE KEY( user_id,email )
    );
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

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

    Default

    Quote Originally Posted by traq View Post
    So, each email must be unique per user, correct? Make the combination unique:
    Code:
    CREATE TABLE contacts(
        user_id INT         NOT NULL,
        email   VARCHAR(50) NOT NULL,
        -- your other columns
        UNIQUE KEY( user_id,email )
    );
    This doesn't work... The user id cannot be unique, it is repeated constantly ..

Similar Threads

  1. Help with a preg_match
    By nicmo in forum PHP
    Replies: 33
    Last Post: 11-29-2010, 09:47 PM
  2. Preg_match help
    By jmace in forum PHP
    Replies: 2
    Last Post: 10-27-2010, 06:41 PM
  3. Remove All Lines Less Than 3 Words Longs
    By young_coder in forum PHP
    Replies: 1
    Last Post: 09-09-2010, 11:04 PM
  4. question about preg_match
    By awakener1986 in forum PHP
    Replies: 1
    Last Post: 02-25-2010, 06:43 PM
  5. preg_match
    By Dennis_Gull in forum PHP
    Replies: 9
    Last Post: 07-30-2007, 03:21 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
  •