Prohibit duplicate emails in MySQL using PHP

Question

Prohibit duplicate emails in MySQL using PHP

Navigation

#1 by (6 votes)
#2 by (2 votes)

2

My situation is as follows , I have a whole email marketing system in PHP and emails are added by .CSV file, however it is adding duplicate values and blank values.

The problem is that , I have 1 table calls n_emails with 2 columns, one called ativo and another email , the ativo column has and should have duplicate values, because it will be the one that will say if the email is active or not, that is, in almost every email the value of the ativo column will be s . The only part I want to forbid duplicate values will be in the email column.

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

Code where you add your emails looks 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>";

 ?>

php mysql

asked by anonymous 08.04.2016 / 13:47

2 answers

2

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

This pre-validation can be elaborated with an array_unique () and some condition validating if 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
   }
}

Output:

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

08.04.2016 / 14:10

Submit multiple forms with AJAX, one at a time as per values inside objects [closed]

score 6 · Accepted Answer

Ensure the uniqueness of the value by the database 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 display 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>";
}