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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts

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

    I'm trying to verify entries into my database using input comparison to a list I've compiled of 9,250 possible valid entries the user can make, that has been imported into a new table containing 3 columns. The column in question is "gamecode" under the table "disc_id".

    If the value they use in the gamecode input field does not exist in the gamecode under disc_id, I'd like it to fail, maybe give them a relative error.

    What are some methods to accomplish this?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    If I follow this correctly, you want to simply use a foreign key constraint. The key relation will require a record in one table that already has a corresponding key in the second. So this second table would insert all the valid records, then the key relation is placed on the first table, and you simply attempt to insert. If it provides no affected rows after the insert, it will trigger an error indicating a key violation, which is simply the answer for no valid foreign key constraint was satisfied.
    If you don't need to store input, than a simple select would tell you if it already exists. It's not clear how you are intending to use the existing disc_id.gamecode in relation to the gamecode via input.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    bemore (03-01-2013)

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    If I follow this correctly, you want to simply use a foreign key constraint. The key relation will require a record in one table that already has a corresponding key in the second. So this second table would insert all the valid records, then the key relation is placed on the first table, and you simply attempt to insert. If it provides no affected rows after the insert, it will trigger an error indicating a key violation, which is simply the answer for no valid foreign key constraint was satisfied.
    If you don't need to store input, than a simple select would tell you if it already exists. It's not clear how you are intending to use the existing disc_id.gamecode in relation to the gamecode via input.
    If the gamecode comes back as existing in the database, I'd like the form to submit correctly. If it returns as non existent, then I'd like the form to not be submit, so I can give the user the option to email their submission for confirmation. They are entering product IDs (gamecode), so I've compiled a list of basically every possible product ID they can use.

    edit: If a foreign key constraint is used here, when disc_id inserts it's gamecode column data to the first table, if the tables are not duplicates of each other how does it make this match? does it use the input fields value after the form is POSTd to do this? Just wondering how it works.
    Last edited by bemore; 02-26-2013 at 10:34 PM.

  • #4
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    cant you just query the db to see if the code is there?
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This sounds like a many to one relationship which you intend to keep track of correct?
    The purpose of the foreign key constraint is a preemptive effort to maintain the integrity of the data within the database (ie: orphan control). So for examples, if I had two tables (ignoring the third as the user table) called gamecodes and usergamecodes, I would have gamecodes with even a single field, which I'll call gamecode. My records look like so:
    Code:
    AAA
    BBB
    CCC
    So three game codes in total.
    Now I have the second table, usergamecodes (also with a key constraint to user), with the columns: userid, gamecodes, and lets say, dateclaimed. userid and gamecode are both foreign keys, and are a composite primary key. My data looks like so:
    Code:
    1 | AAA | 2013-01-17
    1 | CCC | 2013-01-22
    2 | AAA | 2013-02-14
    3 | AAA | 2012-12-12
    If I attempt to insert 1, DDD, 2013-02-14 for example, I would receive the error back from SQL server with the errno value of (lets see if I can get these right from the manual :P), 1452 which is a key constraint violation. This occurred since table gamecode does not include a gamecode of DDD.
    If I were to attempt to insert 1, CCC, 2013-02-14, I would receive an error 1062 since userid 1 and gamecode CCC primary keys are already in use (ie: duplicate).

    The thing I'm not sure of here is if I'm hung up on the INSERT part of your title or not. I'm not sure if these actually need inserting (hence my first reply indicating to just select if you don't need to insert anything).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    bemore (02-28-2013)

  • #6
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thank you for the explanation.
    So, it seems that method would just prevent any data from being INSERTd, whether it exists or not?

    If the value for the text field for gamecode is equal to a value anywhere in the column "gamecode" under "disc_id", the form should be submit and the form's data will be INSERTd into the first table (opl_comp). If there is no match for the gamecode, then the data is not inserted.

    How can SELECT accomplish that?

  • #7
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    PHP Code:
    $result query("SELECT * FROM table WHERE gamecode = ".$_POST['gamecode']);

    if (
    mysql_num_rows($result) == 0){
    //submit form
    INSERT // etc


    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #8
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,352
    Thanks
    61
    Thanked 528 Times in 515 Posts
    Quote Originally Posted by Arcticwarrio View Post
    cant you just query the db to see if the code is there?
    Yes but thats an extra query to run through the mysql server.

    That may not seem like a big issue to you when you think the processor casn run at what seems like the speed of light but when you're on a shared server with hundred / thousands of other users all running thousands of sql statements at the same time, the processor is suddenly limited by bus speeds and worse, the hard drive speed.

    When you hear your PC rumbling, thats your hard drive reading and writing data to the disk platters. This is physically limited to the speed the heads can move across the platters (which being back and forth motions means it must slow down, stop, reverse, speed up, slow down stop etc - all of which guarantee that it will always have a limiting speed). In a web environment with several thousand queries per second being run by multiple websites that hard drive is in constant use reading and writing. For this reason you need to ensure that you use as few sql statements as possible.

    Quote Originally Posted by Arcticwarrio View Post
    PHP Code:
    $result query("SELECT * FROM table WHERE gamecode = ".$_POST['gamecode']);

    if (
    mysql_num_rows($result) == 0){
    //submit form
    INSERT // etc


    So what happens if there are 30 - 40 items input on the ops form that all need checking? - Suddenly you've got 60 - 80 queries to run on the sql server - which can dramatically slow down the webpage.

    The op clearly knows how to use a database and locate data in it but is looking for a clean SQL driven statement which will only run one query.

    If you've never done so Arctic, I suggest you turn on mysql query logging on your sql server and then look at how many queries you have running in your scripts. You may well end up with a nasty surprise as I did years ago when I found one script running 150 queries (consequently grinding both my local and website to a crawl ).
    Last edited by tangoforce; 02-27-2013 at 02:12 PM.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #9
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    lol, i did that a while ago there were very many there now at maximum 5 on any given page
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by bemore View Post
    Thank you for the explanation.
    So, it seems that method would just prevent any data from being INSERTd, whether it exists or not?
    That's not correct.

    As pointed out, I assumed a many to one relationship (for which I converted into a many to many anyways). That is (with the structure I mocked), one game code can be used by many users. The table itself is normalized to allow many to many as well, so than one game code can be used by many users, and a user can use many game code, BUT a game code can only be claimed once per user (with the structure I have). If you need to allow a user to claim a game code many times, I'd suggest that a datetime type be in use and added to the primary key composite.

    The violations I pointed out are caused by duplicate based on primary key (the userid and gamecode are already in use), as well as foreign key violation where gamecode doesn't exist (or userid since you'd constrain both). These violations are designed to protect the database from essentially illogical data (based on the design of it). DB's can only go so far in this regard, but I'd rather it stop me from creating orphans than later attempting to clean them up.

    So with my example above, I can successfully insert the data: 1 | BBB | 2013-02-09 for example since to composite key of 1,BBB are not yet in use.

    Now, if a single gamecode can only ever belong to a single user (ie: claimed once and only once), than you don't want to use a many to many relationship. You use instead a one to one which would be simply a table with a gamecode as the PK, and a related userid. If you prepopulate the gamecode, than you can use a null entry for the userid (you can also punch this down to a three table structure, but sometimes the null would make sense), and you can issue an update via UPDATE gamecodes SET userid = {ausersid} WHERE gamecode = {thegamecode} AND userid IS NULL. The returned result of affected rows will be one if it was successful, and zero if it failed (either the gamecode is invalid or already consumed).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    bemore (02-28-2013)

  • #11
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    So with my example above, I can successfully insert the data: 1 | BBB | 2013-02-09 for example since to composite key of 1,BBB are not yet in use.
    Lack of sleep >>
    Okay, so a foreign key sounds like the solution then..
    There are no user accounts on my website. I included 3 columns in the disc_id table (gamename, gamecode and region) for records sake. I only need to use the gamecode column.

    The sole purpose is as you stated, to protect the DB from visitors submitting illogical data to the database. It is a game compatibility database, and there is no reason for a user to have complete control over the ID of the game they are submitting. Hence the compiled list of 9,250 valid disc IDs (and growing).

    I'm only fresh into SQL, and I'm unsure how to apply a FK constraint between my main table (opl_comp) and the table used for confirming disc IDs (disc_id).

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    So the main table uses a field opl_comp and the disk table uses disc_id as the column names correct?
    The first thing you need to do is make sure that the datatypes between these two columns match identically. Same datatype, same encoding, same length. Once those match, you can issue an alter command for the constraint. I'll call the confirming disk id (the one you prepopulate) table confirmedCodes and the main table main. I'm so creative
    Code:
    ALTER TABLE confirmedCodes ADD CONSTRAINT fk_disc_id_main_opl_comp FOREIGN KEY (disc_id) REFERENCES main(opl_comp) ON DELETE RESTRICT ON UPDATE CASCADE
    Note that to enforce these constraints, you need to be using the INNODB database engine type. The MyISAM will accept the constraint, but will not enforce it.
    ON DELETE and ON UPDATE are optional. By default they should fall to RESTRICT. These are used for cascading options. So if I have related entries to the game code AAA in the confirmedCodes.disc_id to the main.opl_comp, than if I change AAA to GGG, every entry within the main.opl_comp will update to GGG without needing a separate query. The RESTRICT on the delete means I cannot delete AAA if there are entries using AAA within the main.opl_comp. If you want, you can also cascade deletes. The only one I'd suggest not using is SET NULL which would set the corresponding entries to null.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    bemore (02-28-2013)

  • #13
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    So the main table uses a field opl_comp and the disk table uses disc_id as the column names correct?
    The first thing you need to do is make sure that the datatypes between these two columns match identically. Same datatype, same encoding, same length. Once those match, you can issue an alter command for the constraint. I'll call the confirming disk id (the one you prepopulate) table confirmedCodes and the main table main. I'm so creative
    Code:
    ALTER TABLE confirmedCodes ADD CONSTRAINT fk_disc_id_main_opl_comp FOREIGN KEY (disc_id) REFERENCES main(opl_comp) ON DELETE RESTRICT ON UPDATE CASCADE
    Note that to enforce these constraints, you need to be using the INNODB database engine type. The MyISAM will accept the constraint, but will not enforce it.
    ON DELETE and ON UPDATE are optional. By default they should fall to RESTRICT. These are used for cascading options. So if I have related entries to the game code AAA in the confirmedCodes.disc_id to the main.opl_comp, than if I change AAA to GGG, every entry within the main.opl_comp will update to GGG without needing a separate query. The RESTRICT on the delete means I cannot delete AAA if there are entries using AAA within the main.opl_comp. If you want, you can also cascade deletes. The only one I'd suggest not using is SET NULL which would set the corresponding entries to null.
    Thank you for the example.
    I do use SET so visitors can update existing entries using a separate update form.
    SET CASCADE makes it so the same FK rules are enforced on SET?

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Depends on the direction. The CASCADE will work from the "parent", which is our known codes, to the child, which are the claimed codes. So if I change the confirmedCodes.disc_id, anything within the main.opl_comp will update to reflect it.

    The foreign key constraint will always be in effect though. So if I issue an UPDATE main SET opl_comp = 'BBB' WHERE userid=1 AND opl_comp = 'AAA' that would be fine. If I update SET opl_comp = 'GGG', that would fail though since there is no GGG within the confirmedCodes.disc_id.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    bemore (02-28-2013)

  • #15
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    I cloned my tables using the InnoDB datatype. Was this necessary or could I have changed to datatype of the columns themselves?

    You mention the tables needing to basically be copies of each other.
    By size, are you just refer to the number of rows/columns? Or in bytes?

    edit: made a new thread for the off topic question that has come up.
    Last edited by bemore; 02-28-2013 at 04:28 AM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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