Professional Web Applications Themes

Multiple unique keys and handling duplicates on inserts - MySQL

Hi all, i've got a table (user) with 2 unique keys (username and email). Database is Mysql 5.1 with myisam-engine. I'm doing an SELECT for username and email to check for duplicate entries (and display a message to the user). If there are no duplicates, i do an INSERT. Because mysql blocks for some seconds sometimes before the insert (maybe a locking issue), the user resubmitts the html-form, which results in duplicate-key-violations. I dont use transactions for the moment. --- //check if email is in the database $rs = sql("SELECT `username` FROM `user` WHERE `email`='$email'"); if (mysql_num_rows($rs) > 0) $email_exists ...

  1. #1

    Default Multiple unique keys and handling duplicates on inserts

    Hi all,

    i've got a table (user) with 2 unique keys (username and email).
    Database is Mysql 5.1 with myisam-engine.

    I'm doing an SELECT for username and email to check for duplicate entries
    (and display a message to the user). If there are no duplicates, i do an
    INSERT. Because mysql blocks for some seconds sometimes before the insert
    (maybe a locking issue), the user resubmitts the html-form, which results in
    duplicate-key-violations. I dont use transactions for the moment.

    ---
    //check if email is in the database
    $rs = sql("SELECT `username` FROM `user` WHERE `email`='$email'");
    if (mysql_num_rows($rs) > 0)
    $email_exists = true;
    else
    $email_exists = false;

    //check if username is in the database
    $rs = sql("SELECT `username` FROM `user` WHERE `username`='$username'");
    if (mysql_num_rows($rs) > 0)
    $username_exists = true;
    else
    $username_exists = false;

    if (!$username_exists && !$email_exists)
    {
    sql("INSERT INTO `user` ( `user_id`, `username`, `email`) VALUES ('',
    '$username', '$email')");
    }
    else
    // display error to the user
    ---


    I think there are 2 possible solutions:

    1) Use transactions or lock table
    Because i've no experience with transactions, i cannot estimate the
    performance-impact on database and don't sure if myisam-engine supports
    that.

    2) No checks before INSERT, but do INSERT INGORE INTO with check for errors

    Is there any way to get the unique-key-name that caused the violation,
    without parsing string-text?
    ---
    mysql> INSERT INTO `user` (`username`, `email`) VALUES
    ('my-name','net');
    ERROR 1062 (23000): Duplicate entry 'my-name' for key 'username'
    mysql> SHOW ERRORS;
    +-------+------+-----------------------------------------------------+
    | Level | Code | Message |
    +-------+------+-----------------------------------------------------+
    | Error | 1062 | Duplicate entry 'my-name' for key 'username' |
    +-------+------+-----------------------------------------------------+
    1 row in set (0.00 sec)
    ---


    Can someone point me to the right solution?


    Thank you,
    Oliver


    Oliver Guest

  2. #2

    Default Re: Multiple unique keys and handling duplicates on inserts

    Oliver Dietz wrote: 

    Just insert it and check for the 1062 error code. If you get it, the
    data were not unique.

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

  3. #3

    Default Re: Multiple unique keys and handling duplicates on inserts

    Hi,
     

    thank you for posting.
    As i wrote, the question is "why was the data not unique".

    I will do the INSERT with IGNORE, check the affected rows count and if
    affected rows = 0, then i will do a SELECT for username and email ... not
    transactional, but should fix my problem.


    Thank you & best regards,
    Oliver


    Oliver Guest

Similar Threads

  1. CFTransaction , Multiple Inserts and Foreign Keys
    By Belluz in forum Coldfusion Database Access
    Replies: 6
    Last Post: May 24th, 03:36 PM
  2. Handling multiple keys?
    By in forum Macromedia Director Basics
    Replies: 3
    Last Post: April 9th, 12:59 PM
  3. Replies: 6
    Last Post: July 23rd, 03:01 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139