SQL/PHP if input value exists, do not INSERT.

[Fou-Lu]

Nope, the tables aren't copies of each other. They are unique, they simply contain an identical property between them that the referential integrity has to work with. The remaining data can be anything you want them to be. The related properties DO need to be identical in order to apply a constraint though. Identical definition will result in identical number of bytes required for each of the two.
Number of fields is irrelevant, one table can have a single lookup field and another can have 15 fields with only a single one related.

[bemore]

Thank you Fou-Lu for the help, I was able to get it working for the most part using your explained method.

I have one problem, though small I think..
I merged and re-created my tables so they can be same size/rows/columns etc for the FK constraint and as a result my opl_comp table now has 9100 rows containing only ID (auto increment) and GAMECODE (the column where FK is set)
I notice that when I use INSERT INTO, the FK matches allowing an entry correctly, then it inserts the data as a new row. Could this instead update/replace the existing row where the FK matched?

PHP Code:

$tbl_name="opl_comp";
$sql="INSERT INTO $tbl_name(gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)VALUES('$gamename', '$cleangc', '$region', '$mode', '$vmc', '$smb', '$hdd', '$usb', '$notes', '$comp')";
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($sql)); 


[Fou-Lu]

You can issue an INSERT ON DUPLICATE KEY UPDATE SQL Syntax. This way if it cannot insert a record, it will instead update it.
I'd suggest as well that a surrogate auto increment is not needed. Since I'd assume each of the 9100 records is already unique, using the actual code as the primary key would be sufficient.

[bemore]

Originally Posted by Fou-Lu

You can issue an INSERT ON DUPLICATE KEY UPDATE SQL Syntax. This way if it cannot insert a record, it will instead update it.
I'd suggest as well that a surrogate auto increment is not needed. Since I'd assume each of the 9100 records is already unique, using the actual code as the primary key would be sufficient.

I'm using the AI to link to things like update entry forms and game details to call the rows individually. I know I could use the gamecode column for this, but the entries are as ABCD_123.45 and I am unsure right now the best way to validate it for GET to use in the URL.

[Fou-Lu]

The same way as you would with the surrogate.
There's nothing wrong with surrogates per se, but I typically reserve them only for when it becomes more of a management task to juggle large composite keys. My threshold seems to sit at about 3, where anything over that bothers me enough to actually create a surrogate.

[bemore]

Originally Posted by Fou-Lu

The same way as you would with the surrogate.
There's nothing wrong with surrogates per se, but I typically reserve them only for when it becomes more of a management task to juggle large composite keys. My threshold seems to sit at about 3, where anything over that bothers me enough to actually create a surrogate.

I agree. Played around with it, but ABCD_123.45 does not work in the URL
Right now it's like update.php?entry=123

update.php?entry=ABCD12345 would be best, but I have to store it as ABCD_123.45 in the DB

I tried to use regex with GET but could not figure it out.

For now, it is at least working well using the AI column, so perhaps I can work on that change later.

edit: trying to mark this thread as resolved but.. how? can no longer edit OP