mysql auto increment and unique field

Question

I am writing the following command in php:

$query="insert into tableone (user_name, password, name, email) values('$user_name', '$password',   '$name', '$email')";
if(mysql_query($query))
{                                       
    header("Location:thislocation.php");
} else {
    echo '<br><br><font color="red"><strong>Username or EmailId already exists. Please try different Username.<br> If you have forgotten your Password <a href="forgotmypassword.php" title="Forgot Password?" alt="Forgot Password?"><u><i>click here</i></u></a>.</strong></font><br>';
}

Here in mysql-db I have made name and email field as unique and id as auto increment, so that no two people have same registration records. The problem is that, if someone uses same username or email, the query fails and else statement is executed. But, it auto-increments the id, without getting records added in db. If someone after that registers successfully, he gets an id not 2 more than the previous one.

How to get auto-incremented just once?

Answer 1

What you need to do is run an num_rows on it, before you insert it. what num_rows do, it count how many records the SELECT returns, so if it returns 1, there is a place in the database where there are a match, if it returns 0, there are no match

$selectSql = "SELECT * FROM tableone WHERE name= '".$name."' OR email = '".$email."'";
$result = mysql_query($selectSql, $YOUR_DATABASECONNECTION);
$num_rows = mysql_num_rows($result);
if($num_rows){
echo "email or name is already taken";
}else{    
$query="insert into tableone (user_name, password, name, email) values('$user_name', '$password',   '$name', '$email')";
    if(mysql_query($query))
    {                                       
        header("Location:thislocation.php");
    } else {
        echo '<br><br><font color="red"><strong>Username or EmailId already exists. Please try different Username.<br> If you have forgotten your Password <a href="forgotmypassword.php" title="Forgot Password?" alt="Forgot Password?"><u><i>click here</i></u></a>.</strong></font><br>';
    }
}

This is a good way to secure, that the error you're talking about wont happend