Posted by Erwin Moller on 05/23/07 15:59

Jerry Stuckle wrote:

> Phil Latio wrote:
>> I am inserting data into user table which contains 5 fields, sounds
>> simple enough normally but 2 of the fields are designated as UNIQUE. If
>> someone does enter a value which already exists, how do I capture this
>> specific error?
>>
>> Would it make more sense to actually run a SELECT query first and if that
>> returned a result, then I use that for error checking and don't run
>> insert until select returns nothing?
>>
>> Cheers
>>
>> Phil
>>
>>
>>
>>
>>
>
> Phil,
>
> Look at the error code returned by the database.
>
> Most databases return an error code and an error message. The error
> message is great for humans; the error code better for computers, and
> should be unique (IIRC, this one is 1062 for MySQL).
>
> Don't do a select first. It's unnecessary overhead and not guaranteed
> unless you lock the tables first (more overhead). For instance, you
> might search for "john" and not find it - but before you can add "john",
> another process adds it. Now when you try, you get a duplicate, even
> though you checked first.
>

Is that good advise Jerry?
If you use the errormessage, or better errorcode as you suggest, the PHPcode
is specialized for that specific database.
Will, as in your example, mySQL use the same code if you use a different
version? (I hope it does, but I am unsure.)
Not to mention a switch of database.

Probably just a matter of taste, but I always felt that doing the 1 select
to check for the value isn't that much overhead, and you, the programmer,
can handle a conflict of unique constaint gracefully, even if the errorcode
changes or the database changes.
No need to change PHP code, in theory. ;-)

I always regarded the fact that somebody else inserted 'john' between my
select and insert farfetched. If it happens: well, they get an error, but
chances of that happening are small (at least in all situations I have
seen).

Another reason I use SELECT to check for UNIQUE constraints before
inserting:
If you have for example 2 UNIQUE columns (eg nickname, loginname), you'll
get a errorcode, but you cannot tell the user which one was causing it.

just my 2 cent.

Regards,
Erwin Moller

[Back to original message]