PHP/MySQL duplicate records

I've just created a login script using PHP/MySQL. The good news is I have the php page connecting to the database - adding a new user. The database is quite simple consisting of three fields: ID, username, password.  Here's the problem. I tested and entered identical user names and passwords to the database. The database now has two identical records for the same user name. The only difference is the ID field which is set to Auto_Increment. How can I set it up so that if a user name is already taken the db will not accept the duplicate and will instead prompt you to enter a new user name and password? I'm sure this must be quite a simple item I'm overlooking since you see this on any site that requires registration.

Thanks for any help.
fred_belangerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

steelseth12Commented:
$query = mysql_query("SELECT * FROM userstable WHERE username='".$_POST["username"]."'");


if(mysql_num_rows($query)) {


      $message = "USERNAME ALREADY EXIST";
      
}else{

Continue to insert the new user in the database


}
0
Raynard7Commented:
Hi, you could do it this way, or alternatively you could put a unique index in your table such that if there are duplicates your insert statement will fail, you can then take the error message and if it is because of duplicate key then display your prompt, otherwise you can show the appropriate error  message.

additionally never just post $_POST messages into your tables, you can put some very nasty injection using it, if you use

$userName = mysql_real_escape_string($_POST["username"]);

and then

$query = mysql_query("SELECT * FROM userstable WHERE username='$userName'");
0
fred_belangerAuthor Commented:
Thanks for your posts.

Reynard7 I tried your code but I must be missing something. New user names don't get posted to the db nor does it generate an error when  I purposely submit an existing username and password.

This is all new to me and I know I must be thick and do appreciate your patience. Here is the code (following the dbconnection information) as I understand you said it should be:

$userName = mysql_real_escape_string($_POST["username"]);
$password=$_POST['password'];

mysql_connect(localhost,$user,$pass);
@mysql_select_db($db) or die( "Unable to select database");

$query = mysql_query("SELECT * FROM users WHERE username='$userName'");
mysql_query($query);

mysql_close();
?>

For what it's worth, this code works as far as adding to the db but does allow duplicate entries.

$username=$_POST['username'];
$password=$_POST['password'];

mysql_connect(localhost,$user,$pass);
@mysql_select_db($db) or die( "Unable to select database");

$query = "INSERT INTO users VALUES ('','$username','$password')";
mysql_query($query);

mysql_close();
?>

Can you see where I'm going wrong?

Thanks much.

Fred
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Raynard7Commented:
the code i mentioned is only sufficient for the security - for duplicate, you need an or die with error messages at the end of your statement without the @ in front (which supresses errors) and then have a unique key in the database.
0
fred_belangerAuthor Commented:
So the code should be like such:

$userName = mysql_real_escape_string($_POST["username"]);
$password=$_POST['password'];

mysql_connect(localhost,$user,$pass);
mysql_select_db($db) or die( "Unable to select database");

$query = mysql_query("SELECT * FROM users WHERE username='$userName'");
mysql_query($query);

mysql_close();
?>

I always thought the ID was the unique key in the database. Could you enlighten me there. This definitely will take you up to 500 points but maybe even at that I'm asking too much here. Just let me know.

Thanks.

Fred
0
Raynard7Commented:
Hi,

You can have multiple unique keys, but only one primary key - if you have an auto number id then your primary key would be on this field, if you just did something like

ALTER TABLE users ADD KEY `UniqueUserName` (username);

it would add an additional constraint to your table.  

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Probably the best approach is to run the select - use the mysql_num_rows to see if it is there (using the security things mentioned above) and also have the unique constraint in case this does not work for some reason.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.