Posted by Jerry Stuckle on 05/24/07 04:18

Erwin Moller wrote:
> 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.
>

Your code is typically database specific in many ways, anyway. Even the
SQL statements can vary between databases.

But yes, error codes are consistent across all versions of MySQL - and
most of them are consistent across databases also. There are standards
for the majority of the error codes.

> 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. ;-)
>

It is extra overhead, and it does leave an opening for someone else to
sneak in. It's just this type of programming which causes very
difficult to find errors - ones that occur only once every few weeks,
for instance.

> 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).
>

It's not at all far fetched. In fact, I can tell you about a time on a
mainframe where a 4 machine-code instruction sequence would occasionally
fail because it got interrupted in the middle of those 4 statements -
and a flag which was checked by on entry to the sequence got changed.

Remember - you're working with a multitasking system. NEVER assume your
code will not be interrupted!

> 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.
>

Does it really matter? But if it does, then you can go back and
determine which one (or both) is causing the problem.

But proper database normalization also says you won't have a row
dependent on two unique constraints (but it might be dependent on one
constraint with multiple columns).

> just my 2 cent.
>
> Regards,
> Erwin Moller


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]