How to validate duplicate entry in SQL using PHP?

Question

I'm trying to validate and show a message to the user whenever a duplicate entry is submitted. Also, I am using this validation to generate an entry in my database whenever a user registers for the first time. I'm aware my code might be SQL Injection compromised, but I'm not worried about that in this exercise.

My table has a primary key "RUT", it is unique. I need to validate if the user is submitting a RUT already in the database.

Code:

$datos; 

    @$db = mysqli_connect("localhost","root","","speedomart");

    if($db){
        $sql = "insert into cliente values('".$rut."','".$nombre."','".$apellido."','".$correo."','".$pass."')";
        $query = $db->prepare($sql);
        $query ->execute();
        if(mysql_errno() == 1062){
                   $datos = array('mensaje' => "no fue posible insertar datos");
                   echo json_encode($datos);
         }
        else{    
              $sql2 = "insert into carrito values(NULL,'".$rut."')";
              $query = $db->prepare($sql2);
              $query ->execute();    
              $datos = array('mensaje' => "Registrado correctamente");
              echo json_encode($datos);
           };
    }
   else{
          $datos = array('mensaje' => "No hay conexion.");
          echo json_encode($datos);
    };

Possible duplicate of [Best way to avoid duplicate entry into mysql database](https://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — Scuzzy, Jun 22 '17 at 03:05
Possible duplicate of [Insert statement that checks for duplicate before insert](https://stackoverflow.com/questions/8312058/insert-statement-that-checks-for-duplicate-before-insert) — Martins, Jun 22 '17 at 03:07
None of those answers are similar to my problem and question. I'm trying to use the validation for a purpose. — Midori_hige, Jun 22 '17 at 03:10
What do you consider a "*duplicate entry*"? Two clients with the same RUT? — Dinei, Jun 22 '17 at 03:15
Yes, that the primary key. I what to validate that to deliver a message and also generate a new entry to a different table. — Midori_hige, Jun 22 '17 at 03:16
Why don't you `SELECT` from that table an entry `WHERE rut = $rut` and check if it returns something? — Dinei, Jun 22 '17 at 03:19

score 1 · Accepted Answer · edited Jun 22 '17 at 06:16

I am assuming that it is the email which can not be duplicate. So when you submit the form you can first select the data using the particular email id as follows:

    $sql = "select *from table where email ='".$email."'";
            $query = $db->prepare($sql);
            $user_array = $query ->execute();

    if(count($user_array) > 0){
       //You can use insert query here 
    }else{
       //email already exist.
    }

Bholu Bhaiya · Answer 2 · 2020-01-22T06:38:48.127

0

IF Exists(SELECT 1 FROM Table WHERE FieldValue='$') THEN
   --record exists, get ID you need.
   BEGIN
     SELECT TableID FROM Table WHERE FieldValue='$';
   END;
ELSE
BEGIN
    INSERT INTO Table (FieldValue) VALUES('$');
    SELECT LAST_INSERT_ID() AS TableID;
END;
END IF;

edited Jan 22 '20 at 06:38

answered Jun 22 '17 at 06:58

Bholu Bhaiya

167
1
1
12

I happen to like to use `IF EXISTS`, because to me it makes it even more obvious to the (human) reader what the query is supposed to be doing . . . – Mike Robinson Jan 08 '20 at 19:07

score 0 · Answer 3 · answered Jan 08 '20 at 19:06

Two comments:

(1) If "the nature of the data is" that there should be no duplicates for any field or particular combination of fields, then you should define a UNIQUE index so that SQL will never allow a duplicate to be inserted by any means. (You will not be able to create such an index if any duplicates now exist.) This is a "data integrity rule" which SQL will enforce for you.

(2) SQL injection is trivially easy to avoid here, and you should always do so. Simply use parameters in your SQL query text: ? (without quotation marks ... this not a one-character literal string). Now, you simply supply an array of parameter-value substitutions each time you execute the prepared SQL. The parameters will be substituted left-to-right in order of occurrence. This not only avoids injection problems but also is more efficient: the statement does not have to be re-prepared each time. Please just get in the habit of doing this all the time ... you'll be glad you did.

How to validate duplicate entry in SQL using PHP?

3 Answers3