Duplicate entry avoidance on INSERT

[bemore]

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.

$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!

[felgall]

gamecode should be the primary key for the table.

[Old Pedant]

And if for some reason you already have a different primary key on the table (why?), then just add an index:

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.

[bemore]

$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.

$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?

[Old Pedant]

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.

[felgall]

Originally Posted by Old Pedant

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.

[Old Pedant]

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!)