确保用户名和电子邮件地址是唯一的

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.