Website and Database: Check if username exists [duplicate]

First of all when you want the value of a row in the database table to appear only once you need to specify that column as UNIQUE. So start by altering the users table like this:

ALTER TABLE users CHANGE COLUMN username username VARCHAR(255) NOT NULL UNIQUE;

After you've done that, if your script tries to insert a row in the database that contains a username that has already been used, that row won't insert and you won't have duplicates. But that's not enough, you need a way to inform the user that the username they want is already in use, so that's where your PHP script comes in.

Your script isn't working because you call mysqli_query without the database connection object.

There are two ways you can call mysql_query:

• Using the procedural style: mysqli_query($conn, "SELECT * FROM users WHERE username='$username'");
• Going OOP-style: $conn->query("SELECT * FROM users WHERE username='$username'");

You call mysqli_query in a procedural style without giving it a connection object. Change it to one of the above and it will work

I would highly recommend just adding a UNIQUE constraint to your table column.

Run the following query in MySQL:

ALTER TABLE users ADD CONSTRAINT ux_username UNIQUE (username)

Now don't do any checks, simply insert your new user and check $conn->error().

$sql = "INSERT INTO users (`id`, `username`, `password`, `sign_up_date`, `email`, `email_activation`, `permit`, `status`, `first_name`, `last_name`, `dob`, `ipv4`) VALUES (NULL, ?, ?, NOW(), ?, '0', 'c', 'a', ?, ?, '1985-01-01', ?)";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssss', $username, $password, $email, $firstname, $lastname, $ipaddress);
$stmt->execute();

if (empty($conn->error())) {
    header('Location: ../success.php');
    exit; //remember to exit after redirections
} else {
    echo 'Username already exists.';
}

Also I changed your code to properly insert the data. Inserting raw user input is dangerous as there might be a loose single quote or semicolon.

Add a unique constraint to the table.

ALTER TABLE `users` ADD UNIQUE(`username`);

This will prevent the duplicate inserts. You should still try to figure out why you are having issues with that mysqli_num_rows. I suggest you run the query manually and see if it returns results.

You have used mysqli_query in wrong manner either use

$query = mysqli_query($conn,"SELECT * FROM test WHERE username='$username'");

or use

$query = $conn->query("SELECT * FROM test WHERE username='$username'");

FOUND A SOLUTION. I think the guy who came up with the solution deleted his answer. So I will post it here: (By the way, thanks everyone. Your answers were helpful too.)

<?php

$password1 = ($_POST['pass1']);
$password2 = ($_POST['pass2']);
$firstname = ($_POST['fname']);
$lastname = ($_POST['lname']);
$username = ($_POST['user']);
$email = ($_POST['email']);




// Check if any fields are empty
if (empty($_POST['fname']) or empty($_POST['lname']) or empty($_POST['user']) or empty($_POST['email']) or empty($_POST['pass1']) or empty($_POST['pass2'])){
 ?><div class="ERRORBOX"><?php
 // Empty Fields ERROR
echo "You must enter data into ALL of the fields to register. Please try again.";
header( "refresh:5;url=../index.php" );
 ?>
<p>You will be redirected in <span id="counter">5</span> second(s).</p>
<script type="text/javascript">
function countdown() {
    var i = document.getElementById('counter');
    if (parseInt(i.innerHTML)<=0) {
        location.href = 'login.php';
    }
    i.innerHTML = parseInt(i.innerHTML)-1;
}
setInterval(function(){ countdown(); },1000);
</script>
</div>
<?php


} else {
// Check if passwords match
if ($password1 !== $password2) {
?><div class="ERRORBOX"><?php
// Password mismatch ERROR
    echo "You entered two different passwords! Please try again.";
header( "refresh:5;url=../index.php" );
?>
<p>You will be redirected in <span id="counter">5</span> second(s).</p>
<script type="text/javascript">
function countdown() {
    var i = document.getElementById('counter');
    if (parseInt(i.innerHTML)<=0) {
        location.href = 'login.php';
    }
    i.innerHTML = parseInt(i.innerHTML)-1;
}
setInterval(function(){ countdown(); },1000);
</script>
</div>
<?php



} else {    

 // Create connection
          include_once 'credentials.php';
          $conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
          // Check connection
          if ($conn->connect_error) {
             die("Connection failed: " . $conn->connect_error);
          }


          // Check if username exists 
          $username = ($_POST['user']);
          $qry="SELECT * FROM users WHERE username='".$username."'"; 
          $query = mysqli_query($conn, $qry);
          if(mysqli_num_rows($query) > 0){
?><div class="ERRORBOX"><?php
               echo "That username already exists.";
?></div><?php


}else{

          // Check if email exists 
          $email= ($_POST['email']);
          $qry="SELECT * FROM users WHERE email='".$email."'"; 
          $query = mysqli_query($conn, $qry);
          if(mysqli_num_rows($query) > 0){
?><div class="ERRORBOX"><?php
               echo "That email is already registered.";
?></div><?php
               $conn->close();




 }else{

$firstname = ($_POST['fname']);
 $lastname = ($_POST['lname']);
 $username = ($_POST['user']);
 $email = ($_POST['email']);
 $password = ($_POST['pass1']);
$ipaddress = $_SERVER['REMOTE_ADDR']; 

// Create connection
include_once 'credentials.php';
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

 }else{

$sql = "INSERT INTO users (`id`, `username`, `password`, `sign_up_date`, `email`, `email_activation`, `permit`, `status`, `first_name`, `last_name`, `dob`, `ipv4`) VALUES (NULL, '$username', '$password', NOW(), '$email', '0', 'c', 'a', '$firstname', '$lastname', '1985-01-01', '$ipaddress')";


if ($conn->query($sql) === TRUE) {
    header('Location: ../success.php');
exit;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;

}

$conn->close();

}
}
}
}
}

?>