Solved

PHP/MySQL duplicate records

Posted on 2007-04-01
7
1,536 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

617 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