Prohibit duplicate emails in Mysql using PHP

Question

My situation is as follows, I have an email marketing system all in PHP and emails are added per file. CSV, but it is adding duplicate values and blank values.

The problem is that, i have 1 table calls n_emails with 2 columns, a call ativo and another email, the column ativo has and should have duplicated values, because it will be she who will tell if the email is active or not, IE, in virtually all emails the column value ativo will be s. The only part I want the duplicate values to be banned will be in the column email.

In short, I need to block only duplicate emails and blank emails and at the end, after adding the file. CSV, the system shows the count of added records and added records (in case duplicate emails).

Code that adds the emails is like this:

 }

 $tabela = "n_emails";
 $arquivo = 'addemail/x234gqq.csv';

 $arq = fopen($arquivo,'r');

 while(!feof($arq))
 for($i=0; $i<1; $i++){
 if ($conteudo = fgets($arq)){
 $ll++; // $ll 
 $linha = explode(';', $conteudo);// 
 }

 $sql = "INSERT INTO $tabela (ativo, email) VALUES 
 ('$linha[0]', '$linha[1]')";
 $result = mysql_query($sql) or die(mysql_error());
 $linha = array();
 }
 echo "Quantidade de Emails Adicionados: ".$ll; 
 echo "<br><a href='..'>Clique aqui para voltar</a>";

 ?>

Answer 1

Ensure the uniqueness of the value by adding a Unique key Constraint so the database is responsible for knowing which value is repeated or not.

ALTER TABLE tabela ADD CONSTRAINT UNIQUE (coluna)

To show the entered log count, check the result of mysql_query() and make the respective counter increment($sucesso or $falha)

$sucesso = 0;
$falha = 0;
while(!feof($arq)){
    for($i=0; $i<1; $i++){
        if($conteudo = fgets($arq)){
            $ll++; // $ll 
            $linha = explode(';', $conteudo);// 
        }

        $sql = sprintf("INSERT INTO $tabela (ativo, email) VALUES  ('%s', '%s')", $linha[0], $linha[1]);
        if(mysql_query($sql)){
           $sucesso++;
        }else{
           $falha++;
        }
        $linha = array();
    }
    echo "Resumo: Emails duplicados: $falha. Emails Adicionados: $sucesso";
    echo "<br><a href='..'>Clique aqui para voltar</a>";
}

Answer 2

You can add a pre-validation in php and also add Unique as quoted by @rray,

This pre-validation can be elaborated with a unique() array_and some condition validating whether the email is different from empty and active is equal to s.

Example foo.csv:

[email protected];s
[email protected];s
[email protected];s
[email protected];s
;s
[email protected];

Validating:

function getCSV($name) {
   $file = fopen($name, "r");
   $result = array();
   $i = 0;
   while (!feof($file)):
      if (substr(($result[$i] = fgets($file)), 0, 10) !== ';;;;;;;;') :
         $i++;
      endif;
   endwhile;
   fclose($file);
   return $result;
}

function getLine($array, $index) {
   return explode(';', $array[$index]);
}

$foo = getCSV('foo.csv');
$foo = array_unique($foo); // remove os repetidos
for ($i = 0; $i < count($foo); $i++) {
   $line = getLine($foo, $i);
   if (!empty($line[0]) && trim($line[1]) == 's') { // válida se tem valor e é ativo.
      print_r($line); // faça os insert's aqui
   }
}

Exit:

Array ( [0] => [email protected] [1] => s ) Array ( [0] => [email protected] [1] => s ) Array ( [0] => [email protected] [1] => s )