Solved

PHP/MySQL duplicate records

Posted on 2007-04-01
7
1,526 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:fred_belanger
  • 3
  • 2
7 Comments
 
LVL 20

Expert Comment

by:steelseth12
ID: 18832951
$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
 
LVL 35

Expert Comment

by:Raynard7
ID: 18833306
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
 

Author Comment

by:fred_belanger
ID: 18839528
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 35

Expert Comment

by:Raynard7
ID: 18839727
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
 

Author Comment

by:fred_belanger
ID: 18839821
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
 
LVL 35

Accepted Solution

by:
Raynard7 earned 250 total points
ID: 18839852
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now