How can I avoid adding duplicate entries into database table.

[Dhiru1009]

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

[coolsti]

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.

[nomad]

You could capture their IP address using a cookie and then index that IP address for no dup.

nomad

[Markus]

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:

<?php
 
$username = $_POST['username']; // you must escape any input. Remember.
 
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
 
$result = mysql_query($query);
 
if ( mysql_num_rows ( $result ) > 1 )
{
    /* Username already exists */
    echo 'Username already exists';
}
else
{
    /* Username doesn't exist */
    /* .. insert query */
}
 
 

[Dhiru1009]

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:

<?php
 
$username = $_POST['username']; // you must escape any input. Remember.
 
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
 
$result = mysql_query($query);
 
if ( mysql_num_rows ( $result ) > 1 )
{
    /* Username already exists */
    echo 'Username already exists';
}
else
{
    /* Username doesn't exist */
    /* .. insert query */
}
 
 

Problem solved. Thanks markusn00b.

[Dhiru1009]

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.

[flysouth]

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:

$email = $_POST['email']; // you must escape any input. Remember.
 
$query = "SELECT * FROM `register` WHERE `email` = '{$email}'";
 
$result = mysql_query($query);
 
if ( mysql_num_rows ( $result ) > 1 )
 
{
 
    /* Email already exists */
 
    echo 'That E Mail address already exists;
 
     exit;
 
}

[flysouth]

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:

if ( mysql_num_rows ( $result ) > 1 )
 
to:
 
if ( mysql_num_rows ( $result ) > 0 )

Makes sense when you think about it!

[Artnessde]

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');
}