How not to write duplicate data in MySQL with PHP? [duplicate]

You can also put the field you do not want to repeat as UNIQUE :

ADD UNIQUE INDEX 'campo_da_tabela_UNIQUE'

There is also a way to directly execute a query in MYSQL that will only insert the record if it does not exist. It is a Conditional Insert .

Example:

$query = "INSERT INTO usuarios(login, senha) 
    SELECT '$login_que_nao_pode_duplicar', 123456 
    FROM DUAL
    WHERE NOT EXISTS(SELECT login FROM usuarios WHERE login = '$valor_que_nao_pode_duplicar')";

In this case, the values of the first SELECT are the values that are to be entered. The value of the second SELECT is the value that is checked if it already exists.

This query, when executed, will return% with% affected row if the value passed in the second SELECT (which has to be the same as the first SELECT) does not yet exist in your table.

So much so that if you run it again this same query, it will return% with% affected rows.

Another example with PDO:

  $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

   $nome = 'Wallace';
   $stmt = $pdo->prepare("INSERT INTO usuarios(login, senha) SELECT '$nome', '123456' FROM DUAL WHERE NOT EXISTS(SELECT login FROM usuarios WHERE login = '$nome')");
   $stmt->execute();

   print_r($stmt->rowCount());

When I open the page, print_r displays "1". When I refresh the page, it displays "0". When you rename the $ name variable, you would get $ 1 again. This means that the query only entered the record when they did not exist. :)

So you can run this query quietly; and if it returns "0", you can display an alert message to the user, for example.

To do this, just check if the data you want to insert already exists in the database or not, here is a basic example.

<?php
if(!$conect=mysqli_connect('localhost','root','','tabela_')) die ('erro ao conectar');
#Recolhendo os dados do formulário
$dado1 = mysqli_real_escape_string($_POST['dado1']);
$dado2 = mysqli_real_escape_string($_POST['dado2']);
# Verificando apenas um campo, no caso dado1.
$sql = $conect->query("SELECT * FROM tabela_ WHERE dado1='$dado1'");
if(mysqli_num_rows($sql) > 0){
echo "Este usuário já existe";
exit();
} else {
 if(!$conect->query("INSERT INTO tabela_ (dado1,dado2) VALUES ('$dado1','$dado2')")) die ('Os dados não foram inseridos');
 echo "Dados inseridos com sucesso";
}

In this example, if the value of the form's given1 field already exists in this table row, it is not added again.

This can also be solved when creating the table.

 CREATE TABLE IF NOT EXISTS tabela_(
  dado1 VARCHAR(255) NOT NULL,
  UNIQUE KEY(dado1) # Aqui especificamos que a linha dado1 será única, e não poderá ter outra de igual valor nessa mesma tabela.
 );

Create a CONSTRAINT .

ALTER TABLE table ADD CONSTRAINT cunique_table_customer_primary UNIQUE (column1, column2)