Solved

PHP/MySQL duplicate records

Posted on 2007-04-01
7
1,535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

734 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