SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    handling database constraints

    I wasn't sure if I should have posted this in the PHP or in the Advanced PHP forum, but since it's more a theoretical kind of question, I've posted it here.

    The question concerns user input / form validation and database constraints. Let me give an example that I think most people will have dealt with.

    Say I have a form that allows an administrator to grant a new user access to a web control panel. The administrator has to give that user a username and a password to login with. The username must be unique. In a database we can enforce this uniqueness with a UNIQUE constraint on the column. Although not possible in MySQL, in other DBMS's one can also add other types of constraints to a column, such as a constraint limiting the length of a username.

    When the administrator tries to create a user account with a username that is already in use, i.e. violates the UNIQUE constraint, he should be notified of this error, preferably with a 'friendly' message like "The username you chose is not unique" instead of the database error message "Warning: column username not UNIQUE blabla". The database error message will be returned after an INSERT- or UPDATE-query failed.

    The way I see it, there are two possibilities to code this. One way is to execute the INSERT-query. check the database's error message (if any) and report an error to the administrator. The other way is to execute a SELECT for a unique username and if that query returns any rows, you report the error to the administrator and don't execute the INSERT-query.

    What do you think is the best way to handle this kind of logic? Should it be done primarily by the database server or in the, what I guess you could call, 'client' (the script that uses the database)? How do you do this in your scripts?

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, it's better in MySQL ( although it could go in it's parent forum).

    Anyways, is UNIQUE() available on MySQL? Not familiar with it though I must say I use more PHP functions than MySQL mostly because it's so flippin' hard to find anything on mysql.com.

    Aaron
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Addict richard_h's Avatar
    Join Date
    May 2002
    Location
    London
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I make a select before an insert or update. I think this solution makes cleaner more legible code than the alternative of checking for error messages.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    definitely i prefer the method of going ahead with the INSERT and trapping the error message if there was a duplicate (or other error)

    the reason is based on performance

    do you expect the INSERT to succeed most of the time, or do you expect the INSERT to fail most of the time?

    let's say out of 100 INSERTs, a total of 5 will be rejected

    by doing just the INSERT, you will call the database 100 times

    by doing a SELECT before the INSERT, you will call the database 195 times

    as the saying goes, "you do the math"




    rudy

  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As Sketch mentioned, MySQL does provide a UNIQUE constraint. What's more, if you violate it, the error code will be 1062 (just trivia you pick up along the way )

    So you could watch for that error message in particular and handle it in a special way;

    PHP Code:
    if ( mysql_errno() == "1062" ) {
        return (
    'User already exists');

    Alternatively you could use mysql_affected_rows() to check to see if a row was inserted.

  6. #6
    SitePoint Addict richard_h's Avatar
    Join Date
    May 2002
    Location
    London
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your not using an abstraction layer then Harry's method looks good otherwise your options will obviously be limited.

  7. #7
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There seem to be advantages and disadvantages to either solution. About checking error messages / error codes, I thought about that as well. One obvious problem is that not each database type gives the same error codes, but a bigger problem is that you can not find out which column was unique if there is more than one (for example: username and e-mailaddress both have to be unique). The only way to figure this out is to actually parse the error message, but this does not seem like a very elegant solution to me.

    I wish databases like MySQL would throw some kind of exception objects in the programming language that uses it. Then you could simpl do something like this (in PHP):

    PHP Code:
    switch ($error->getType())
    {
     case 
    'UNIQUE_CONSTRAINT_VIOLATION':
       if (
    $error->getViolatedColumn() === 'username')
       {
         
    $sendToAdministrator "The username you chose was not unique";
       }
       elseif (
    $error->getViolatedColumn() === 'password')
       {
         
    $sendToAdministrator "The e-mail was not unique";
       }

    What good are MySQL error messages when we can not easily extract the parameters we need from that error message when we want to reformat the message? Just a thought..

  8. #8
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wish databases like MySQL would throw some kind of exception objects in the programming language that uses it.
    Would it not be possible to implement you're own database error handling class, with sub classes for different databases? Then you could return an instance in case of error?

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Rudy: running a SELECT beforehand is basically 100% wasted effort. Why even have constraints in the database if you are going to perform the work yourself?

    What needs to happen in MySQL is the ability to write your own error messages, like you can do in most other DBMS’ today. In ASE or SQL Server (I know it can be done in Orable but don’t know the syntax) I can create an error message and bind it to any number of constraints, bypassing the system-supplied (and somewhat ugly as people have noted) error messages.

    That way when an insert violates some constraints all I have to do is dump out the error message provided by the DBMS!

  10. #10
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That sounds cool, I wish MySQL would do that But it's IMO only a partial solution.

    Thinking in terms of an N-tier application, the error messages generated by the database may need to be 'displayed' in various formats. For example, a web service application might want to give it's client a different error message then a web brower application.

    There's also the issue of internationalisation, presenting error messages in different languages.

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Captain Proton
    There's also the issue of internationalisation, presenting error messages in different languages.
    In the DBMS' I spoke of multi-language support is included -- meaning once you set the language to something it will display error messages from that language set (of course you have to define them as such).

    As far as the first is concerned, you can also bind more than one error message to a constraint, although your situation does not make much sense to me. I suppose you could differentiate them via error code or severity.

  12. #12
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FYI, there's talking here http://www.zend.com/zend/week/week125.php of an overhaul of the PHP MySQL functions including an "Object Orientated and plaininterface". That might at least be a start towards better error handling in PHP.

  13. #13
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, I read that. I hope you're right

    Perhaps I will a request for this to the php developers mailinglist.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •