Organizational Research By

Surprising Reserch Topic

check for duplicate entry vs use pdo errorinfo result using -'php,mysql,pdo'

check for duplicate entry vs use pdo errorinfo result  using -'php,mysql,pdo'

I have a MySQL table which has a field for email addresses which is defined as unique.  For this example, let's say that all my form does is allow a user to insert their email address into the table.

Since the email field is unique, the query should fail should they try to enter the same email twice.  I'm curious about the trade-offs between between the two scenarios:

1)  Run a quick SELECT statement before performing the insert.  If the select returns results, inform the user, and do not run the INSERT statement.

2)  Run the INSERT statement, and check for a duplicate entry error

// snippet uses PDO
if (!$prep->execute($values))
    $err = $prep->errorInfo();
    if (isset($err[1]))
        // 1062 - Duplicate entry
        if ($err[1] == 1062)
            echo 'This email already exists.';

Also, please assume normal use, meaning that duplicate entries should be minimal.  Therefore, in the first scenario you obviously have the overhead of running an additional query for every insert, whereas in the second you're relying on error handling.  

Also, I'm curious to hear thoughts on coding style.  My heart says 'Be a defensive programmer!  Check the data before you insert!' while my brain says 'Hmmm, maybe it's better to let MySQL take care of checking the data for you'.

EDIT - Please note this isn't a "How do I do this" question, but rather a "Why should I do this a particular way" question.  The little code snippet I included works, but what I'm curious about is the best way to solve the problem.

asked Sep 7, 2015 by rajesh
0 votes

Related Hot Questions

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.