Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Question Duplicate entry avoidance on INSERT

    I'm working on a game compatibility database. Here is the submission form.
    http://oplinfo.hj.cx/insert.php

    I'd like to avoid duplicate entries into the database. Here is my INSERT INTO.
    Code:
    $sql="INSERT INTO $tbl_name(gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)VALUES('$gamename', '$gamecode', '$region', '$mode', '$vmc', '$smb', '$hdd', '$usb', '$notes', '$comp')";
    When a user makes a submission, the gamecode they submit is already unique (because all manufactured games use a unique game code) and relative only to that entry.

    So, if user A submits a game to the database that has the gamecode "ABCD-12345", and user B comes along and tries to supply the same gamecode for their entry, I'd like that submit to fail.

    Any tip appreciated!

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    gamecode should be the primary key for the table.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    And if for some reason you already have a different primary key on the table (why?), then just add an index:
    Code:
    CREATE UNIQUE INDEX gamecode_index ON yourTableName(gamecode);
    But I have a question: WHY are you using $tbl_name in that query?

    PLEASE don't tell us that means you have more than one table with this same structure.

    If so, odds are 20 to 1 you have a poor database structure.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Code:
    $tbl_name="opl_comp";
    Currently I only use one table, but plans for more.
    What if I had the same table structure for other tables representing different versions of software compatibility... why would this be bad structure?

    I use a primary key column called "ID", which is set to A_I in order to call each row individually so users can perform edits and update the list publicly.

    Code:
    $game_id = isset($_GET['game_id']) ? (int)$_GET['game_id'] : 0;
    
    $rowsID = ($rows['id']); $editlink = "update.php?game_id=$rowsID"; $src = '/files/images/edit.png'; echo '<a href="'. $editlink .'"><img style="border:none;" src="'. $src .'" /></a>';}
    My question is, how do I perform an INSERT or something similar that will avoid adding the submission to the database if the user's value for "gamecode" already exists under the column "gamecode".

    I have added an index as suggested, but how can I use this to avoid duplicate entries on INSERT?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Well, the best idea would be to drop your useless ID column and then set GAMECODE as your primary key.

    Adding an auto_increment as a primary key is kind of a "last resort" when there is no other good candidate. Since you stated that GAMECODE must be unique, then clearly it is the perfect choice for a primary key.

    In any case, whether primary key or unique key, if you try to insert another record with the same GAMECODE value, you *will* get an error. Trap that error in your (assumed) PHP code, and presto. You are there.

    **********

    There are many reasons NOT to have multiple tables with the same structure and many reasons to combine them into one table.

    Think about it: If you need multiple tables only because of one feature ("compatibility") then simply add one column to your single table (named "compatibility" or whatever makes sense to you) and now you have all the benefits of multiple tables plus the huge benefit of a unified table.

    Let's just take one "for instance":
    Find me all games of any kind of compatibility, etc., with the word "TREK" in their name and that have "UK" in their region name.
    If you had separate tables, you would have to search *ALL* the tables for that information. (Either one at a time or in a huge UNION query.) With a single table, you make one simple query on one table and you are done.

    And *PLEASE* don't fall victim to that old "But I'll have too many entries in such a big table" plaint. MySQL can QUITE EASILY handle *MILLIONS* of records in a single table without batting an eye.

    By the way, if your GAMECODE does *NOT* imply a particular "compatibilty" and you want to have games with the same GAMECODE but differing COMPATIBILITY values, the answer is easy: Make a *COMBINED* PRIMARY KEY, consisting of both those columns.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    There are many reasons NOT to have multiple tables with the same structure and many reasons to combine them into one table.
    About the ONLY reason I can think of for setting up multiple files with the same structure is where you have a high percentage of the records that are effectively "dead" and are still there only as an archive of past information. Rather than having an active/archived flag field it might be worth splitting them into a separate table as then almost all of the queries need only deal with the subset of still active records. That split would work really well with say one million active records and 49 million archived records as then most searches are only looking at the 2% of the total data.

    For any situation other than that simply adding an extra field is the most efficient alternative.


    It is really amazing the number of people who add unnecessary id fields to their database tables and then wonder why they have trouble detecting duplicate inserts - which would not occur if they used the right key in the first place.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    We are in violent agreement. On all points. How strange <grin style="sheepish" />

    And I amazed at the number of people who don't realize that a primary key can be a composite key.

    I actually have some tables with only 3 fields where the primary key is...all three fields! (And because of MySQL's mildly braindead use of indexes, it's mightily important what order the field are listed in the composite primary key. But oh man, when it's done right does it give wonderful performance!)
    Last edited by Old Pedant; 02-22-2013 at 07:10 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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