PHP SQL Preventing Duplicate User names: Catching Exception vs Select Query

Question

For preventing duplicate user names from being entered into the database and notifying the user, is it more standard/preferred to use exception catching when inserting or select query before inserting?

1. Exception catching: if I try to insert the user input and the user name already exists then the SQL database will throw a primary key constraint violation exception. I can catch it if that happens and do whatever.

2. Select Query: if it returns any tuples matching the user name then I won't bother with the insert. Then I can display the error message.

I suppose the main pro of using Exceptions here is there is less queries and lines (better speed?). However, I don't think this is an exceptional case since duplicates probably occur fairly often.

Answer 1

In the very least, you should use option 1 - handle primary key constraint violation - when possible since the database is the last point of entry. Duplication is still possible if you check for duplicates in code (albeit highly unlikely) since there is a delay between when the initial SELECT is returned and the INSERT statement is sent back, and another user could have performed the same insert in that time window.

However, it is likely more efficient to run a select first to see if the record exists, rather than blindly letting the insert run every single time.

So I would recommend both.

Answer 2

If you use Exception catching method i.e. try to insert then keep in mind one thing when ever the Exception occurs your counter for auto_increment column will increase lets take an example you have inserted one record successfully and its id is 1 then you try to insert the same information you got the Exception then you try to insert another data with different data it will be inserted but the id no. for this time will be 3 not 2 as 1 was incremented when Exception occurred so keep in mind if you play with Exception case you will loose these id no.s when when Exception occurs .If id counter is not your concerned then go ahead with this option.

But i prefer to select first then insert

Hope it makes sense

Answer 3

Unless, your database querying layer provides multiple Exception classes (for each SQL states - in case of relational databases) you should not use Exception catching; in that case you need to rely on the exceptions' messages - it could change over time.

Answer 4

I don't think there actually is a "standard" of doing this like this. Either you query, then insert, or you go for the INSERT / exception.

Both works fine if applied correctly.

I usually prefer to let the database do this task. Alter your table so the field you want to be unique is actually a unique index. When you want to insert the data, and it is a duplicate, you'll receive an error which can be used to decide what to do next. Or you might use an ON DUPLICATE KEY (if available on the RDBMS of your choice) if you want to do another table operation instead, if needed.

This avoids having problems on the database side, if not only your application writes into those tables. Even if you query first, you should do this in order to have a solid table.

Answer 5

How about the IGNORE clause:

CREATE TABLE `users` (
`username` VARCHAR(16),
PRIMARY_KEY (`username`)
);

INSERT IGNORE INTO `users`
(`username`)
VALUES ('Foo Dude');

This will generate a warning but won't terminate.

Apparently you can use the following to capture that warning in PHP 5.1.0 and beyond:

function exception_error_handler($severity, $message, $errfile, $errline)
{
    if (error_reporting()) { // skip errors that were muffled
        throw new ErrorException($errstr, $errno, 0, $errfile, $errline);
    }

    // Or handle in some other way, like filtering for the WARNING
    // that is generated by the INSERT IGNORE.
}

set_error_handler("exception_error_handler");

But, I haven't tried this. Check out the PHP manual for "ErrorException" [http://php.net/errorexception] and especially the comments -- consider implementing your own "ErrorException" class where you filter for the WARNING generated by duplicate key inserts when using INSERT IGNORE.

OR

Apparently error_get_last() will also get warnings.

AND

If you don't want these warnings to show up in the log, then either use error_reporting() to suppress them, or try putting an '@' in front of the query function [use with extreme caution 'cuz this will suppress ALL errors/warnings!]:

@mysqli_query($query);