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 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  2. #17
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    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)); 
    Last edited by bemore; 02-28-2013 at 02:58 PM.

  3. #18
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  4. Users who have thanked Fou-Lu for this post:

    bemore (02-28-2013)

  5. #19
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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.

  6. #20
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  7. #21
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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
    Last edited by bemore; 03-01-2013 at 10:56 AM.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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