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

[antonyf]

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:



$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);

[antonyf]

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..



$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);

[traq]

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

$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);

[antonyf]

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..



$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..

[traq]

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.


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 (http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html) column so the database wouldn't allow duplicate entries. If you then want duplicate entries to be ignored, use the IGNORE (http://dev.mysql.com/doc/refman/5.1/en/load-data.html) option when you load the file, so such rows will be skipped:
LOAD DATA LOCAL INFILE 'file.name' IGNORE …
-- or REPLACE instead, depending on what you want to happen

[antonyf]

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..



// 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..

[traq]

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:
CREATE TABLE contacts(
user_id INT NOT NULL,
email VARCHAR(50) NOT NULL,
-- your other columns
UNIQUE KEY( user_id,email )
);

[antonyf]

So, each email must be unique per user, correct? Make the combination unique:
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 ..