preventing duplicate row insertion in php/mysql

Question

how do you prevent the entry of duplicate records in php mysql? validating input before insertion to the table.

i am currently building a user registration section for my website but want to present them with an error if a duplicate username or email address is found in the database.

EDIT
This is what I have to far to insert rows... how would I implement both these answers here:

  <?php
        if (array_key_exists('confirm',$_POST)) {

                $Cuser = $_POST['Cuser']; 
                $Cemail = $_POST['Cemail']; 
                $Cpassword = $_POST['Cpassword'];
                $md5password = md5($_POST['Cpassword']);

                mysql_query("INSERT INTO tbl_users (`username`,`email`,`password`)
VALUES ('$Cuser', '$Cemail', '$md5password')"); 

                echo "Thank you. The user <b>".$Cuser."&nbsp;(".$Cemail.")</b> has been successfully added to the database.<p>

                <a href='myaccount.php' class='form'>Return</a>";       

                exit;
        }
        ?>

I'm guessing that I could implement it all in an if...else statement?

OMG Ponies · Accepted Answer · 2010-02-25 02:34:07Z

17

Define unique constraints for the username and email columns:

ALTER TABLE your_table ADD CONSTRAINT uk_username UNIQUE (username)
ALTER TABLE your_table ADD CONSTRAINT uk_email UNIQUE (email)

If the value attempting to be insertered or updated already exists in the table, MySQL will return an error stating the query violates the appropriate unique constraint (possibly both). It's up to you to setup PHP to handle this gracefully.

answered Feb 25, 2010 at 2:34

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:34:04Z

3

Before inserting, run a SQL statement something like

SELECT * FROM users WHERE users.name = :name OR users.email = :email

(using parametrized SQL) and count the number of rows returned.

If there's more than 0, there's a duplicate.

OMG Ponies suggested constraints on those two fields, which is also a very good idea for preventing you from making a mistake with the database. However, if you insert a duplicated, all that constraint will get you is a database error message that you may or may not want to try to parse.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Feb 25, 2010 at 2:34

Jesse Millikan

3,1451 gold badge22 silver badges32 bronze badges

1 Comment

methuselah Over a year ago

i used the sql statement along with an if else statement. mysql_select_db($database_speedycms, $speedycms); $query_usercheck = "SELECT * FROM tbl_users WHERE username='$user' OR email='$email'"; $usercheck = mysql_query($query_usercheck, $speedycms) or die(mysql_error()); $row_usercheck = mysql_fetch_assoc($usercheck); $totalRows_usercheck = mysql_num_rows($usercheck); then if ( $totalRows_usercheck > 0 ) display an error message to prevent any duplicates from being added otherwise allow database insert

methuselah · Accepted Answer · 2010-02-25 03:11:15Z

2

i used the sql statement along with an if else statement.

mysql_select_db($database_speedycms, $speedycms);
$query_usercheck = "SELECT * FROM tbl_users WHERE username='$user' OR email='$email'";
$usercheck = mysql_query($query_usercheck, $speedycms) or die(mysql_error());
$row_usercheck = mysql_fetch_assoc($usercheck);
$totalRows_usercheck = mysql_num_rows($usercheck);

then

if ( $totalRows_usercheck > 0 )

display an error message to prevent any duplicates from being added

otherwise allow database insert

answered Feb 25, 2010 at 3:11

methuselah

13.3k53 gold badges179 silver badges339 bronze badges

Collectives™ on Stack Overflow

preventing duplicate row insertion in php/mysql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related