Preventing duplicate username entries when registering (PHP/MySQL

Question

I have looked around and have seen many examples of it done but have unfortunately not been able to implement it into the code I am currently using.

I am wanting to prevent users from having the same username when they register on my page but the code I am using allows the entries to be the same.

How can I modify the below code to prevent this from happening?

 <?php
            require('db.php');
            // If form submitted, insert values into the database.
            if (isset($_POST['username'])){
                $username = $_POST['username'];
                $email = $_POST['email'];
                $password = $_POST['password'];
                $username = stripslashes($username);
                $username = mysql_real_escape_string($username);
                $email = stripslashes($email);
                $email = mysql_real_escape_string($email);
                $password = stripslashes($password);
                $password = mysql_real_escape_string($password);
                $trn_date = date("Y-m-d H:i:s");

                $res_login = $conn->query("select * from users where username='$username'");
                if($res_login -> num_rows > 0)
                {
                      echo "Username is already exists please try with another username";
                }
                else
                {
                      $query = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
                      $result = mysql_query($query);
                      if($result)
                      {
                                  echo "You are registered successfully.</h3><br/>Click here to <a href='login.php'>Login</a></div>";
                      }
                      else
                      {
                                  echo "error in registration";
                      }
                }
         }
        ?>

any help would be highly appreciated.

why don't you use username as a primary key in your database? — bhansa, Jun 18 '16 at 11:21
The simplest way is to search your table for username before insert. — u_mulder, Jun 18 '16 at 11:21
@u_mulder could you show me an example of how this is done, sorry I am quite new to this. — bcssmc, Jun 18 '16 at 11:23
@Bhansa Its not a good approach. PK should be an `int`, for fast searching and sorting etc. Unique constraint can be used for `username` like columns. — Shaharyar, Jun 18 '16 at 11:29

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

The best approach would be to set username as a primary key in your database and it would give you error for duplicating.

Answering what you ask with your code, would be to search for the username, if it exists, dont add.

Anyway, your question is duplicated. Here

edited May 23 '17 at 11:58

Community

1
1

answered Jun 18 '16 at 11:31

matiaslauriti

4,495
4
29
37

Bhavin · Answer 2 · 2016-06-20T07:41:54.043

Best way to check about uniqueness is that you have to set user name as primary key in your database but if want to manually check then check it using select query that username is already exist in your database or not ?

      <?php
            require('db.php');
            // If form submitted, insert values into the database.
            if (isset($_POST['username'])){
                $username = $_POST['username'];
                $email = $_POST['email'];
                $password = $_POST['password'];
                $username = stripslashes($username);
                $username = mysql_real_escape_string($username);
                $email = stripslashes($email);
                $email = mysql_real_escape_string($email);
                $password = stripslashes($password);
                $password = mysql_real_escape_string($password);
                $trn_date = date("Y-m-d H:i:s");

                $res_login = mysql_query("select * from users where username='$username'");
                $sql_num = mysql_num_rows($res_login);
                if($sql_num > 0)
                {
                      echo "Username is already exists please try with another username";
                }
                else
                {
                      $query = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
                      $result = mysql_query($query);
                      if($result)
                      {
                                  echo "You are registered successfully.</h3><br/>Click here to <a href='login.php'>Login</a></div>";
                      }
                      else
                      {
                                  echo "error in registration";
                      }
                }
         }
        ?>

Please use prepared statement for best programming practice. Visit this link : PDO::prepare manual

thanks this seems to be the best answer, however the above code does not work for me — bcssmc, Jun 18 '16 at 11:42
You're mixing APIs here, which is why OP says it isn't working. OOP MySQLi and the old `mysql_` (as OP originally used). — Qirel, Jun 18 '16 at 15:22

score 0 · Accepted Answer · answered Jun 18 '16 at 11:46

You can also set username as unique key index which will prevent duplicate rows to enter in table.

and review code below

<?php
    require('db.php');
    // If form submitted, insert values into the database.
    if (isset($_POST['username'])){
        $username = $_POST['username'];
        $email = $_POST['email'];
        $password = $_POST['password'];
        $username = stripslashes($username);
        $username = mysql_real_escape_string($username);
        $email = stripslashes($email);
        $email = mysql_real_escape_string($email);
        $password = stripslashes($password);
        $password = mysql_real_escape_string($password);
        $trn_date = date("Y-m-d H:i:s");
        $query = "INSERT into `users` (username, password, email, trn_date) VALUES ('$username', '".md5($password)."', '$email', '$trn_date')";
        $result = mysql_query($query);
        if (mysql_errno()) {
           if (mysql_errno()==1062) {
              echo "Username already taken.";
           }
        }else{
            if($result){
            echo "You are registered successfully.</h3><br/>Click here to <a href='login.php'>Login</a></div>";
        }
        }
    }else{
?>

Hope this works for you....

thank you this works perfect! I really appreciate the help on this — bcssmc, Jun 18 '16 at 11:52

Preventing duplicate username entries when registering (PHP/MySQL

3 Answers3