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.

any ideas would be appreciated... thanks!

Answer 1

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.

Answer 2

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.

Answer 3

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?

Answer 4

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