SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Catching different unique keys in MySQL

    I was hoping someone will be able to help me with the following. I've got a table that looks like this:

    Code:
    CREATE TABLE `user` (
      `userid` mediumint(9) NOT NULL auto_increment,
      `email` varchar(255) NOT NULL default '',
      `customerid` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`userid`),
      UNIQUE KEY `customerid` (`customerid`),
      UNIQUE KEY `email` (`email`)
    ) TYPE=MyISAM AUTO_INCREMENT=1 ;
    As you can see it has two unique keys and a primary key. I've added the unique keys because I want the customerid and the e-mail address to be unique.

    In most situations my tables only have one unique key, so I can something like this to catch the unique error and handle it:

    PHP Code:
    $db->exec ("INSERT INTO table (field1, field2) VALUES ('one', 'dup')");

    if (
    $db->errno == 1062) {
    echo 
    'Already exists, please go back and try a different one';
    }

    // ... 
    But how would I do this with two unique keys, and be able to know which key triggered the unique error?

    MySQL does return the key number, like so:

    Code:
    Duplicate entry 'foo' for key 3
    But that isn't really helpful, since I have no real way of knowing what key 3 or key 2 is. I'd rather have the actual fieldname. Another problem is that I'd have to parse the error message.

    Any suggestions or tips? Or should I just hard code the key 3 and key 2 in my script?
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog

  2. #2
    What a twist! Kings's Avatar
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    954
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've solved it already. I pretty much went with my original thought (hardcode the key numbers and parse the error message), but it's all abstracted away in my DB library, so I can do this:

    PHP Code:
    if ($crud->user->unique_error('customerid') == true) {
    echo 
    'CustomerID is not unique';
    die();
    }

    if (
    $crud->user->unique_error('email') == true) {
    echo 
    'E-mail address is not unique';
    die();

    Works perfectly!
    Dennis Pallett - NoCertainty - My Personal Weblog
    The Web Network: ASPit | PHPit | WebDev-Articles
    Blogs: TalkFones | Holidayzer | PHPit Blog


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
  •