How to prevent duplicate usernames when people register using Php?

Question

I have been making a login/register system.The only problem I am running into is how to make it so that users cannot register with duplicated usernames. I have looked around and have seen many examples of it but my code is not working.

I wanted to prevent users from having the same username when they register on my page. Any help is appreciated.

MySQL Table:

Create_User:
ID  |  upload_img |   fullname   |  username  |   role   | password    
1   |  <img>      |   Billy      |  billyuser |   User   | test123
2   |  <img>      |   Admin One  |  adminone  |   Admin  | adminonpass

HTML:

<form action="create_user.php" method="post" enctype="multipart/form-data">

       Profile Photo (Optional)<br>
       (Max file size:2MB, file type:jpeg, jpg, png)<br><br>
       <input type="file" name="fileToUpload" id="fileToUpload" required/><br><br>
       Full Name: &nbsp;
       <input type="text" name="fname" id="fname" size="40" maxlength="70" placeholder="Please enter your full name"><br><br>
       Username: &nbsp;
       <input type="text" name="uname" id="uname" size="35" maxlength="62" placeholder="Please enter your username"><br><br>
       Role: &nbsp;
       Customer<input type="radio" name="role" id="role" value="Customer">
       Administrator<input type="radio" name="role" id="role" value="Admin"><br><br>           

         Password: <br>
         <input type="password" name="pword" id="pword" size="35" pattern=".{6,}" placeholder="Password" title="Six or more characters"><br><br>
         <input type="password" name="cfmpword" id="cfmpword" size="35" placeholder="Confirm Password"><br><br>


         <button type="submit" name="signbtn" style="border:none; background:white; padding:0px">
            <img src="button/Sign%20up.png">
         </button><br>
         <a href="signin.html">Back to Sign In</a>

  </form>

Php:

<?php

if (isset($_FILES["fileToUpload"]["name"])) {

      $file=$_FILES["fileToUpload"]["name"];
      $target="uploadfile/" . $file;
      $fname=$_POST['fname'];
      $uname=$_POST['uname'];
      $role=$_POST['role'];
      $pword=$_POST['pword'];

      $conn=mysqli_connect("localhost", "root", "" , "SportFacility");

      $sql_insert = "INSERT into create_user (upload_img, fullname, username, role,  password) 
      values ('$file', '$fname' , '$uname' , '$role' ,  '$pword' )";

      $result=mysqli_query($conn, $sql_insert);

      $allowedType=array("image/jpeg", "image/jpg", "image/png");
      if(in_array ($_FILES["fileToUpload"]["type"] ,$allowedType))
      {
           echo "<script type='text/jscript'>alert('File type is acceptable')</script>";
      }
      else
      {
          echo "<script type='text/jscript'>alert('Invalid file type')</script>";
          exit();
      }
      if($_FILES["fileToUpload"]["size"] < 2000000)
      { 
          echo "<script type='text/jscript'>alert('File size is acceptable')</script>";
      }
      else
      {
          echo "<script type='text/jscript'>alert('File is too large')</script>";
          exit();
      }
      $directoryfile=move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target);

      if($result)
      {
          header("Location: login.html");
      }
      else
      {
          header("Location: register.html");
      }

mysqli_close($conn);

}

 if (isset($_POST['uname']))
      $uname=$_POST['uname'];
      $conn=mysqli_connect("localhost", "root", "" , "SportFacility");
      $sqluser="SELECT username FROM create_user WHERE username='$uname' ";
      $qresult=mysqli_query($conn, $sqluser);
      $count=mysqli_num_rows($qresult);
      if($count)
      {
           echo "Username is already taken";
      }
      else
      { }
 ?>

I believe you have to check $count for number as $count will hold number of result the query returned like :`if($count>0){...}` — Ambrish Pathak, Feb 05 '17 at 05:44

score 0 · Answer 1 · answered Feb 05 '17 at 05:33

0

First, prevent duplicate usernames in the database by using a UNIQUE index or constraint. In MySQL this is done with this command:

ALTER TABLE `tableName` ADD UNIQUE `indexName` ( `columnName` )

This will prevent your database being in a bad state (i.e. with multiple users with the same name). But you need to proactively handle this in your PHP code to prevent users from getting an ugly MySQL error message, you can do this by first checking to see if a username is taken or not with a simple SELECT 1 FROM tableName WHERE column = value.

Finally, never use string concatenation! Use parameterisation! Someone can easily hack your site by putting SQL commands in the HTML form!

answered Feb 05 '17 at 05:33

Dai

141,631
28
261
374

It's a bit more complex than that. Checking for existing usernames and inserting a new one should be done within a transaction. – Tim Biegeleisen Feb 05 '17 at 05:35
Also, in practice the unique index might be on _two_ columns, the username and the tenant. – Tim Biegeleisen Feb 05 '17 at 05:36
@TimBiegeleisen The OP doesn't seem to be building a multi-tenant database. There is no mention of tenancy in his question or posted schema. – Dai Feb 05 '17 at 06:03
The bottom line is that checking for an existing user and inserting a new user should happen within a transaction, not separately. – Tim Biegeleisen Feb 05 '17 at 06:04

score 0 · Answer 2 · answered Feb 05 '17 at 06:14

If you want to check duplicate username then your first step is check duplication before insert username into table.

your php file

$conn=mysqli_connect("localhost", "root", "" , "SportFacility");

if (isset($_FILES["fileToUpload"]["name"])) {
  if (isset($_POST['uname']))
  $uname=$_POST['uname'];
  $sqluser="SELECT username FROM create_user WHERE username='$uname' ";
  $qresult=mysqli_query($conn, $sqluser);
  $count=mysqli_num_rows($qresult);
  if($count > 0)
  {
       echo "Username is already taken";
  }
  else
  { 

  $file=$_FILES["fileToUpload"]["name"];
  $target="uploadfile/" . $file;
  $fname=$_POST['fname'];
  $uname=$_POST['uname'];
  $role=$_POST['role'];
  $pword=$_POST['pword'];

  $sql_insert = "INSERT into create_user (upload_img, fullname, username, role,  password) 
  values ('$file', '$fname' , '$uname' , '$role' ,  '$pword' )";

  $result=mysqli_query($conn, $sql_insert);

  $allowedType=array("image/jpeg", "image/jpg", "image/png");
  if(in_array ($_FILES["fileToUpload"]["type"] ,$allowedType))
  {
       echo "<script type='text/jscript'>alert('File type is acceptable')</script>";
  }
  else
  {
      echo "<script type='text/jscript'>alert('Invalid file type')</script>";
      exit();
  }
  if($_FILES["fileToUpload"]["size"] < 2000000)
  { 
      echo "<script type='text/jscript'>alert('File size is acceptable')</script>";
  }
  else
  {
      echo "<script type='text/jscript'>alert('File is too large')</script>";
      exit();
  }
  $directoryfile=move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target);

  if($result)
  {
      header("Location: login.html");
  }
  else
  {
      header("Location: register.html");
  }

 mysqli_close($conn);

}

How to prevent duplicate usernames when people register using Php?

2 Answers2