How not to write duplicate data to Mysql with PHP?

Question

I’m learning PHP and, to begin with, I’m doing a phonebook project, with name, DDD and phone.

However, before new data is entered, how do I check if the same phone number is already registered?

Answer 1

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 will be entered. The value of the second SELECT is the value that is checked if it already exists.

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

So much so that if you run it again this same query, it will return ZERO affected lines.

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 changing the $name variable name, you would get $1 again. This means that the query only entered the record when they did not exist. :)

Thus, you can execute this query quietly; and, if you return "0", you can display an alert message to the user, for example.

Answer 2

To do this just check whether the data you want to enter 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 data1 field already exists in this table row it is not added again.

This can also be solved by 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.
 );

Answer 3

You can also put the field you don’t want repetition as UNIQUE:

ADD UNIQUE INDEX `campo_da_tabela_UNIQUE`

Answer 4

Create a CONSTRAINT.

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