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 — Scuzzy
– Scuzzy, Commented Jun 22, 2017 at 3:05
Possible duplicate of Insert statement that checks for duplicate before insert — Martins
– Martins, Commented Jun 22, 2017 at 3:07
None of those answers are similar to my problem and question. I'm trying to use the validation for a purpose. — Midori_hige
– Midori_hige, Commented Jun 22, 2017 at 3:10
What do you consider a "duplicate entry"? Two clients with the same RUT? — Dinei
– Dinei, Commented Jun 22, 2017 at 3:15

Felix · Accepted Answer · 2017-06-22 06:16:53Z

1

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.
    }

edited Jun 22, 2017 at 6:16

Felix

5811 gold badge14 silver badges34 bronze badges

answered Jun 22, 2017 at 4:54

PHP Geek

4,0632 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Robinson · Accepted Answer · 2020-01-08 19:06:31Z

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.

Bholu Bhaiya · Accepted Answer · 2020-01-22 06:38:48Z

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, 2020 at 6:38

answered Jun 22, 2017 at 6:58

Bholu Bhaiya

1671 gold badge1 silver badge12 bronze badges

1 Comment

Mike Robinson Over a year ago

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 . . .

Collectives™ on Stack Overflow

How to validate duplicate entry in SQL using PHP?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related