Ensure username and email address are unique

Question

I am trying to verify whether or not a username and email address exist in my database and have tried to do a if, elseif, else statement to no avail.

I want to first run a check to see if the username is fine - obviously if not, an echo statement will appear. If the username doesn't exist, run an elseif statement to see if the email address is unique - again if not, another echo statement will appear. For the final statement, if all other conditions return false, I want to run the below code so that the user's input is submitted to the database.

I initially tried to declare two variables with a statement to check if the username=$username and email_address=$email_address then check to see if the number of rows returned from a mysqli_query is more than 1 for the username. I entered an elseif statement with the same but for email address but then I had an else statement with the below code in {} brackets.

I have deleted the original code because too many errors were thrown up, and was probably too convoluted and messy when a more elegant way to do what I was exists.

Any help would be appreciated.

if(isset($_POST['submit']))
    {
$sql = "INSERT INTO users (first_name, last_name, username, email_address, password,    gender, city, country, verification_code, verified, sign_up_date) VALUES (
'$first_name',
'$last_name',
'$username',
'$email_address',
'$password',
'$gender',
'$city',
'$country',
'$verification_code',
'1',
'$sign_up_date')";
    $result = mysqli_query($conn,$sql);
    header("Location:confirmation.php");
}   

Answer 1

What you want is an integrity check on the data. You should do this check inside the database. The simplest way is with unique constraints/indexes:

create unique index unq_users_username on users(username);
create unique index unq_users_email on users(email);

If you attempt to insert or update a row so it violates these constraints, then your data modification step will fail with an error.

Answer 2

You need to create an index for them. Use The following command to create the index:

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Check This Link for more info: https://www.w3schools.com/sql/sql_create_index.asp

Answer 3

You could write a function to check your database first for example:

$errors = []; // you can add errors to this array.
if (isset($_POST['submit']))
{
    // first do your validation here against empty values and invalid email

    $sql = "SELECT * from users where email='$email' and username='$username'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
       $errors[] = "Username and email has been taken.";
    }

    if (!empty($errors))
    {
       // loop through your errors and echo them
    } else {
      // insert your values into the database 
    }
}