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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Raynard7Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.