Ask a Question
Advertise on boostr.in
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
¬†¬†¬†¬†$err = $prep->errorInfo();
¬†¬†¬†¬†¬†¬†¬†¬†// 1062 - Duplicate entry
¬†¬†¬†¬†¬†¬†¬†¬†if ($err == 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.
Sep 7, 2015
to add a comment.
Your name to display (optional):
Email me at this address if my answer is selected or commented on:
Email me if my answer is selected or commented on
Privacy: Your email address will only be used for sending these notifications.
To avoid this verification in future, please