By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,751 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,751 IT Pros & Developers. It's quick & easy.

How can I avoid adding duplicate entries into database table.

P: 24
Hi guys,

I have a problem. I have created a login system where user can register their login details before they login to website, to register they have to provide there username,password and email address but I have problem that I can register same user thousand times. How can I stop registering same username second time. Any help will be appreciated.

Thanks
Aug 28 '08 #1
Share this Question
Share on Google+
8 Replies


100+
P: 310
You can basically do this in a few ways.

Your database tables which store the user information, such as user name and perhaps email address, etc., should be set up with a unique key so that the same user could not be added to the table twice. For example, if you identify a user by name + email address, you would set up the table so that these two columns together are a unique index. From my experiences with various sites, however, it seems that either the user name (i.e. screen name) or email address alone is used as the unique index.

Setting up the database to not allow a second entry using unique indexes is how you enforce that someone only registers once. But you also wish to show this to the user in a user-friendly manner. So what you can do when someone attempts to register is to first check the database to see if that user already exists, and if so, you send back a page with an error message saying that the user is already registered.

If you set up your database table with unique indexes, you could perhaps simply try to perform the insert of the user when he/she tries to register. If there is a duplicate key violation, the insert will not occur, and your database will return with an appropriate error code (1062 for MySQL). You could then have your script examine the error code and if it is the one for a duplicate key error, you then can send the error message of already being registered to the user.
Aug 28 '08 #2

nomad
Expert 100+
P: 664
You could capture their IP address using a cookie and then index that IP address for no dup.

nomad
Aug 28 '08 #3

Markus
Expert 5K+
P: 6,050
I'm sure this was covered in the same post you made a day or two ago.

You run a 'where' clause in a mysql query. Using the result of this query and a mysql_num_rows() check you can find whether the username is already present in the database.

Take this for example:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. $username = $_POST['username']; // you must escape any input. Remember.
  4.  
  5. $query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
  6.  
  7. $result = mysql_query($query);
  8.  
  9. if ( mysql_num_rows ( $result ) > 1 )
  10. {
  11.     /* Username already exists */
  12.     echo 'Username already exists';
  13. }
  14. else
  15. {
  16.     /* Username doesn't exist */
  17.     /* .. insert query */
  18. }
  19.  
  20.  
Aug 28 '08 #4

P: 24
I'm sure this was covered in the same post you made a day or two ago.

You run a 'where' clause in a mysql query. Using the result of this query and a mysql_num_rows() check you can find whether the username is already present in the database.

Take this for example:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. $username = $_POST['username']; // you must escape any input. Remember.
  4.  
  5. $query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
  6.  
  7. $result = mysql_query($query);
  8.  
  9. if ( mysql_num_rows ( $result ) > 1 )
  10. {
  11.     /* Username already exists */
  12.     echo 'Username already exists';
  13. }
  14. else
  15. {
  16.     /* Username doesn't exist */
  17.     /* .. insert query */
  18. }
  19.  
  20.  
Problem solved. Thanks markusn00b.
Aug 29 '08 #5

P: 24
You can basically do this in a few ways.

Your database tables which store the user information, such as user name and perhaps email address, etc., should be set up with a unique key so that the same user could not be added to the table twice. For example, if you identify a user by name + email address, you would set up the table so that these two columns together are a unique index. From my experiences with various sites, however, it seems that either the user name (i.e. screen name) or email address alone is used as the unique index.

Setting up the database to not allow a second entry using unique indexes is how you enforce that someone only registers once. But you also wish to show this to the user in a user-friendly manner. So what you can do when someone attempts to register is to first check the database to see if that user already exists, and if so, you send back a page with an error message saying that the user is already registered.

If you set up your database table with unique indexes, you could perhaps simply try to perform the insert of the user when he/she tries to register. If there is a duplicate key violation, the insert will not occur, and your database will return with an appropriate error code (1062 for MySQL). You could then have your script examine the error code and if it is the one for a duplicate key error, you then can send the error message of already being registered to the user.
I am able to fix the problem. Thanks coolsti.
Aug 29 '08 #6

P: 2
Thanks for that code - but I found that it did not stop the script processing and submitting the data from the form to the DB - it just flashed up the message 'Username already exists' and carried on regardless!

The fix was simply to add 'exit' which causes the script to stop, only where the parameter (in my case I am checking email addresses) is already present in the DB, in which case the message that previously flashed up for a second now remains until the user goes back to the form and inserts a different email address - see below:

Expand|Select|Wrap|Line Numbers
  1. $email = $_POST['email']; // you must escape any input. Remember.
  2.  
  3. $query = "SELECT * FROM `register` WHERE `email` = '{$email}'";
  4.  
  5. $result = mysql_query($query);
  6.  
  7. if ( mysql_num_rows ( $result ) > 1 )
  8.  
  9. {
  10.  
  11.     /* Email already exists */
  12.  
  13.     echo 'That E Mail address already exists;
  14.  
  15.     exit;
  16.  
  17. }
Nov 24 '11 #7

P: 2
Oops!

After testing this extensively I found that the code above (with 'exit;') does not pick up the first duplicate parameter - the form can be sublitted TWICE with the same email address - after that it shows the warning message.

The problem is again simple to fix - I changed this line:

Expand|Select|Wrap|Line Numbers
  1. if ( mysql_num_rows ( $result ) > 1 )
  2.  
  3. to:
  4.  
  5. if ( mysql_num_rows ( $result ) > 0 )
Makes sense when you think about it!
Nov 24 '11 #8

P: 13
First add a good unique keys to username and e.g. to the email field and an auto increment primary key (auto incrementing user-id eg.)

To avoid unneccessary selects you could insert the new data with the ignore Attribute like

$sql = "INSERT IGNORE INTO [TABLENAME] SET [FIELDNAME] = '[VALUE]' ...";
$res = mysql_query($sql, $resource);

and then after sending the query, check the last_insert_id().

if( (int) mysql_last_insert_id($resource) == 0 )
{
die('username or email already taken');
}
Nov 26 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.