Thread: MySql newbie.

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Colombo, Western, Sri Lanka
    Posts
    41
    Rep Power
    14
    I'm in the process of creating a product catalog. Each product has a unique code which has to be entered by the user at the time of entering new products into the table.

    How can I prevent duplicate codes from being inserted into the table??

    Thanks,

    Ranesh.

  2. #2
  3. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    do a select query with the new number, if the query returns any rows (mysql_num_rows($qh) > 0) display error, if not, insert the record into the table
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Colombo, Western, Sri Lanka
    Posts
    41
    Rep Power
    14
    What is $qh ?

    (Please excuse my ignorance....I'm a newbie.)

    thnx.

    Ranesh.
  6. #4
  7. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    that's your query handler variable
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    81
    Rep Power
    15
    You definitely don't want to be doing it the way Ryan's just suggested unless you can possibly help it (sorry Ryan!). There are some major issues with the method that's been outlined which I'll detail in a moment. First, here's how you'd be better off doing it, if possible.

    All you need to do to ensure that the DB doesn't get multiple rows with the same ID (which should be unique) is to specify that the column containing that unique ID can only contain unique values. This is done by specifying a key on the column -- it can either be the primary key (basically, just a unique indexed key), or simply a unique key. To add a unique key to your table use the following:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    ALTER TABLE mytable ADD UNIQUE (my_unique_column)
    [/code]

    If you're going to be doing a lot of lookups via this column, you can also make it all a lot faster by adding an index:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    ALTER TABLE mytable ADD INDEX (my_unique_column)
    [/code]

    So, now the reasons for not using the other method suggested:

    1) You're relying on application logic to maintain the integrity of your data -- what if at some point you or someone else writes some other application logic that works with the same table, but forgets to add that integrity checking (or simply implements it incorrectly)

    2) For every insert, you're needing at least two roundtrips to the server (one to check if the row exists and then one to insert it). This will double the time it takes for an insert to occur, and as a round trip can be quite slow (especially if at some point the DB is moved elsewhere on a network or something) it can have a serious impact on application performance

    3) Probably most importantly, it doesn't work. You introduce an unpleasant race condition because you're working in a multi-user environment. Imagine the following sequence of events where User A and User B are both trying to insert '123' into the table:
    User A checks if '123' is free. Query says yes.
    User B checks if '123' is free. Query says yes.
    User A inserts '123'
    User B inserts '123'!
    Because you can't control the interleaving of query/insert statements across multiple users (without using transactions, which are sloooow), it's possible for multiple users to check for the availability of a value before any of them insert, leading to the DB erroneously reporting to multiple users that they can use a single given value.

    As you can imagine, situations like this are absolute buggers to debug, so you want to try to avoid them before they happen. Using a unique key approach to this problem, only one round trip is needed on average -- if the unique key constraint is violated (i.e. two people try to insert the same value), the DB will chuck an error back at you, which you can then trap and alert the user about to get a new key (or do what you will).
  10. #6
  11. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    Chris,
    No problem, I always welcome a better way to do things, and after your explanation I have seen the error of my ways. This will also prompt me to learn about PHP's error handling features.

Similar Threads

  1. Newbie needing help sending data to mysql database
    By blackface in forum PHP Development
    Replies: 3
    Last Post: February 13th, 2004, 12:26 AM
  2. newbie Q - is there an undo function in mysql?
    By cubanallstar83 in forum MySQL Help
    Replies: 2
    Last Post: February 5th, 2004, 07:32 AM
  3. Newbie MySQL Query Help
    By Agent_Oorange in forum MySQL Help
    Replies: 1
    Last Post: February 2nd, 2004, 03:12 PM
  4. Upgrade from access to mysql
    By karib1 in forum MySQL Help
    Replies: 1
    Last Post: December 16th, 2003, 12:27 AM

IMN logo majestic logo threadwatch logo seochat tools logo