Prevent duplicate data in Mysql with php

Question

I create simple a Registration Form that connect with mysql

html code :

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <link href="css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    
    <div class="container">

<form method="post" enctype="multipart/form-data>">
<div class="form-group">

<label for="username"> User Name</label>
<input type="text" name="username" id="username">
</div>
<br>
<div class="form-group">
<label for="password"> password</label>
<input type="password" name="password" id="password">
</div>
<br>
<div class="form-group">
<label for="age"> Age</label>
<input type="text" name="age" id="age">
</div>
<br>

<button class="btn btn-primary btn-block" name="b_send"> send data</button>

</form>



</div>

    <script src="js/bootstrap.min.js"></script>
    <script src="js/jquery.js"></script>

</body>
</html>

mysql : dbname is = final table is = students

in table students i have column (username) and i set it to UNIQUE ,

in php code i make connection :

    <?php
    
if ($_SERVER['REQUEST_METHOD']=="POST"){

    if(isset($_POST['b_send'])){

        $username= $_POST['username'];
        $password=$_POST['password'] ;
        $age=$_POST['age'];
        
            try{
$conn=new PDO ("mysql:host=localhost; dbname=final",
                "admin","admin");
                
$sendvalues=$conn->exec("insert into students(username,password,age)
                        values('$username','$password','$age')"); 
                                                                                        

}

catch(PDOExeption $e){

    echo "error in data send";

}
         

}

}

?>

now , i want to make check , if user has enter duplicate name that already exist in column (username), it prevent sending data to db and give user error message

thanks in advance

You should consult a tutorial or manual. You are open to SQL injections with this code. If you check `$sendvalues` error code you could see if it was a duplicate error or a different error. Alternatively you could send an AJAX check then tell user prior to register it already is present. This would allow a bruteforce attack though that all usernames could be discerned. — user3783243, Dec 24 '21 at 13:59
Before the INSERT, perform a SELECT to see if the username exists. — Chris Haas, Dec 24 '21 at 14:09
*i want to make check , if user has enter duplicate name that already exist in column (username), it prevent sending data to db and give user error message* Makes no sense. Now you send 1 query and see does the name is a duplicate. In the case which you want to make you will send 1 query (and see does the name is a duplicate) or 2 queries. Where do you want to find a profit? — Akina, Dec 24 '21 at 14:27
Side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Dec 24 '21 at 14:30
@Akina i want when user fill form and press send button it return error if username is already exist — MohamedSalem, Dec 24 '21 at 14:31
Inspect the error. If it indicates a unique constraint violation, print an error message telling the user the name was already taken. — sticky bit, Dec 24 '21 at 14:32
@ChrisHaas you mean like : $check=$conn->query("select * from students"); if so , what is if statement ? — MohamedSalem, Dec 24 '21 at 14:39
*i want when user fill form and press send button it return error if username is already exist* Analyze the error (code, message) returned. If it is duplicate violation then the name exists, if not then some other problem occures. Now you ignore this information which is obviously bad practice. — Akina, Dec 24 '21 at 14:44
Does this answer your question? [check for duplicate entry vs use PDO errorInfo result](https://stackoverflow.com/questions/10774943/check-for-duplicate-entry-vs-use-pdo-errorinfo-result) — noah1400, Dec 24 '21 at 15:14
@Akina i really not understand what you mean ?!! you just copy what i write without any useful information !! — MohamedSalem, Dec 24 '21 at 16:26
No! You write absolutely backward stages ordering. You want to check does it present then save if not present. I tell that you must save unconditionally then check does it was saved or it was not saved due to the presence (or another reason). — Akina, Dec 24 '21 at 16:37

noah1400 · Answer 1 · 2021-12-24T15:07:42.567

Either your keep using insert into and catch an error if one occured while executing the query like this:

try {
   $conn->exec("insert into students(username,password,age)
                        values('$username','$password','$age')"); 
   // do other things if successfully inserted
} catch (PDOException $e) {
   if ($e->errorInfo[1] == 1062) {
      // duplicate entry, do something else
   } else {
      // an error other than duplicate entry occurred
   }
}

Because you set the column username to UNIQUE the execution will throw an error when trying to insert a duplicate entry.

or you can use insert ignore and completely ignore an error message. read more about insert ignore here

hello @noah1400 nothing happen when i register with existing account , it not record in database but no error shown to user about duplicate entry — MohamedSalem, Dec 25 '21 at 09:01

Prevent duplicate data in Mysql with php

1 Answers1