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 20
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts

    ON DUPLICATE KEY UPDATE Conditions

    I've this

    Code:
    INSERT INTO opl_comp(gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)VALUES('$gamename', '$cleangc', '$region', '$mode', '$vmc', '$smb', '$hdd', '$usb', '$notes', '$comp')
    ON DUPLICATE KEY UPDATE gamename=('$gamename'), region=('$region'), mode=('$mode'), vmc=('$vmc'), smb=('$smb'), hdd=('$hdd'), usb=('$usb'), notes=('$notes'), comp=('$comp')";
    Which is updating the existing row where the supplied gamecode value (foreign key) matches, though it also allows the row to be cleared and overwritten by submitting the same gamecode value again.

    I'm trying to achieve the same thing as above, except only when (gamename)="".
    I've tried a few things such as CASE WHEN & THEN VALUES, but must be doing it wrong.
    Goal is to allow the existing row where the FK (gamecode) matches to be updated but only if the gamename column in that row is blank.

    Shed of light appreciated

  • #2
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    738
    Thanks
    20
    Thanked 85 Times in 85 Posts
    thats only looking to update a record, if it were blank then the row would be wmpty and you would need to INSERT it

    or maybe try another blank key:

    PHP Code:
    INSERT INTO opl_comp(gamenamegamecoderegionmodevmcsmbhddusbnotescomp)VALUES('$gamename''$cleangc''$region''$mode''$vmc''$smb''$hdd''$usb''$notes''$comp'WHERE gamename'' 
    ON DUPLICATE KEY UPDATE gamename=('$gamename'), region=('$region'), mode=('$mode'), vmc=('$vmc'), smb=('$smb'), hdd=('$hdd'), usb=('$usb'), notes=('$notes'), comp=('$comp'"; 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,487
    Thanks
    279
    Thanked 32 Times in 31 Posts
    Shouldnt this be in the MYSQL section?

  • #4
    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
    Yes it should be. Moving to mysql.
    I'm not sure if you can use a case within an on update clause, but you can use an if:
    Code:
    INSERT INTO opl_comp(gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp) VALUES ('$gamename', '$cleangc', '$region', '$mode', '$vmc', '$smb', '$hdd', '$usb', '$notes', '$comp')
    ON DUPLICATE KEY UPDATE
       gamename = IF(gamename = '', $gamename, gamename),
       region = IF(gamename = '', $region, region),
       ...
    Old pedant may have some better ideas for this as well.
    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-06-2013)

  • #5
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks for the suggestions, I will try these tips out when I am home this evening.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    Personally, for something like this I would opt to make a SELECT query first, to check the existing conditions, and then do either an INSERT or UPDATE (or nothing at all!) as appropriate. Surely this doesn't happen often enough that doing two queries instead of one will make any real difference on the site's performance?

    But I don't see why Fou-Lu's trick won't work.
    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.

  • #7
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    So, I tried the following

    PHP Code:
    $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')
    ON DUPLICATE KEY UPDATE 
    gamename = IF(gamename = '', $gamename, gamename),
    region = IF (gamename = '', $region, region),
    mode = IF(gamename = '', $mode, mode),
    vmc = IF(gamename = '', $vmc, vmc),
    smb = IF(gamename = '', $smb, smb),
    hdd = IF(gamename = '', $hdd, hdd),
    usb = IF(gamename = '', $usb, usb),
    notes = IF(gamename = '', $notes, notes),
    comp = IF(gamename = '', $comp, comp)"

    And it gives me

    Code:
    Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hack - Part 1 - Infection, gamename), region = IF (gamename = '', U, region), mo' at line 3
    Same deal with Arcticwarrio's suggestion. I tried changing a few things, but unable to overcome the syntax error.

    @Old_Pendant you're correct in that it may not happen very often, though I'd just prefer previous entries to be safe, as I cannot control whether or not a visitor checks to see if a game has been added before adding it again and overwriting the previous entry with possibly less compatibility information.

    How can I incorporate conditions using a SELECT query before an INSERT and ON DUPLICATE KEY UPDATE? I've yet to try anything like this. My experience with SQL is limited.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    Missing the apostrophes around the PHP variables. Why did you think you could omit them in the UPDATE section if you needed them in the INSERT section???

    But I'd do this:
    Code:
    // ensure no sql injection and also put the apostrophes around each value
    // so that you only have to do this once!
    function clean( $val )
    {
        return "'" . mysql_real_escape_string( $val ) . "'"
    }
    // do it for each value:
    $gamename = clean($gamename);
    $cleangc = clean($cleangc);
    $region = clean($region);
    $mode = clean($mode);
    $vmc = clean($vmc);
    $smb = clean($smb);
    $hdd = clean($hdd);
    $usb = clean($usb);
    $notes = clean($notes);
    $comp = clean($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) "
         ON DUPLICATE KEY UPDATE 
         gamename = IF(gamename = '', $gamename, gamename),
         region = IF (gamename = '', $region, region),
         mode = IF(gamename = '', $mode, mode),
         vmc = IF(gamename = '', $vmc, vmc), 
         smb = IF(gamename = '', $smb, smb), 
         hdd = IF(gamename = '', $hdd, hdd), 
         usb = IF(gamename = '', $usb, usb), 
         notes = IF(gamename = '', $notes, notes), 
         comp = IF(gamename = '', $comp, comp)";
    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.

  • Users who have thanked Old Pedant for this post:

    bemore (03-06-2013)

  • #9
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    That was indeed the problem.

    PHP Code:
    $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')
    ON DUPLICATE KEY UPDATE 
         gamename = IF(gamename = '', '$gamename', gamename),
         region = IF (region = 'O', '$region', region),
         mode = IF(mode = '', '$mode', mode),
         vmc = IF(vmc = '', '$vmc', vmc), 
         smb = IF(smb = '', '$smb', smb), 
         hdd = IF(hdd = '', '$hdd', hdd), 
         usb = IF(usb = '', '$usb', usb), 
         notes = IF(notes = '', '$notes', notes), 
         comp = IF(comp = 'untested', '$comp', comp)"

    Seems to be working as needed this way
    Very helpful.. silly to not include the apostrophes, my mistake. But at least it all worked out and I learned something new!
    Using gamename column for every IF did allow it to UPDATE, but only the game name column would be updated. Rest of the submission form inputs are ignored, and the rest of the columns would be left blank.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    Still say a SELECT followed by either INSERT, UPDATE, or nothing would be better.

    If you did that all in a Stored Procedure, it would still only involve one call from PHP.

    And why did you reject my idea about adding the mysql_real_escape_string??
    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.

  • #11
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Still say a SELECT followed by either INSERT, UPDATE, or nothing would be better.

    If you did that all in a Stored Procedure, it would still only involve one call from PHP.

    And why did you reject my idea about adding the mysql_real_escape_string??
    I'm not sure how to go about using SELECT to get the same results as IF gets me. The idea is to always UPDATE the existing row, but only if there is nothing in the row's gamename column.

    I'm already using
    PHP Code:
    if(isset($_POST['gamename'])) {
    $gamename mysqli_real_escape_string($link$_POST['gamename']);   
    } else {
    $gamename "None";

    I see that your method is cleaner and a lot less code, as I use the above for each input name, but
    PHP Code:
    function clean$val )
    {
        return 
    "'" mysql_real_escape_string$val ) . "'"

    gives me a syntax error on }

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    The semicolon on the end of the line is missing.
    Code:
        return "'" . mysql_real_escape_string( $val ) . "'";
    I don't use PHP so I tend to make typos like that.
    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.

  • #13
    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
    Whoa whoa, you're mixing mysql and mysqli libraries here.
    Since you are using mysqli, you can just bind the variables and be done with it. No need to escape them at all. Old Pedant's function is good and clean for mysql library, but it won't be as clean with the mysqli since the mysqli resource is (rightfully at that) not global, so it would need either globalization or passing to the function (preferable). So the end result is simply a function call that will be near identical to the use of the original code. Instead of
    $gamename = mysqli_real_escape_string($link, $_POST['gamename']);
    , you'd have $gamename = clean($link, $_POST['gamename']);, which if the only purpose is to execute mysqli_real_escape_string, I'd beg question on the overall usefulness of breaking it down into a separate function.

    Statement that up instead. I've never done a statement with an on duplicate update, but I can't see why it would be any different. I'll just pull the VALUES instead:
    PHP Code:
    <?php
    // pull your connection in here
    // Gather ye' variables with a simple pull.  Walk the $_POST through a stripslashes if get_magic_quotes_gpc() is enabled:
    if (isset($_POST['gamename'], $_POST['gamecode'], /*...etc */))
    {
        
    $gamename $_POST['gamename'];
        
    $gamecode $_POST['gamecode'];
        
    // etc
        
    $tbl_name 'yourtable';
        
    $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)
            VALUES 
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE 
                gamename = IF(gamename = '', VALUES(gamename), gamename),
                region = IF (gamename = '', VALUES(region), region),
                mode = IF(gamename = '', VALUES(mode), mode),
                vmc = IF(gamename = '', VALUES(vmc), vmc),
                smb = IF(gamename = '', VALUES(smb), smb),
                hdd = IF(gamename = '', VALUES(hdd), hdd),
                usb = IF(gamename = '', VALUES(usb), usb),
                notes = IF(gamename = '', VALUES(notes), notes),
                comp = IF(gamename = '', VALUES(comp), comp)"
    ;  
        if (
    $stmt mysqli_stmt_prepare($link$sql))
        {
            
    mysqli_stmt_bind_param($stmt'ssssssssss'$gamename$gamecode$region$mode$vmc$smb$hdd$usb$notes$comp);
            
    mysqli_stmt_execute($stmt);
            
    mysqli_stmt_close($stmt);
        }
        else
        {
            
    printf("Failed to create a statement: %s" PHP_EOLmysqli_error($link));
        }
    }
    Like that. Untested, and I wrote it in procedural to match what you have, so I can't be 100% sure that I got it right :P

    I noticed as well that you have 2x different queries. I wrote this one with the assumption that it can go ahead and update so long as the gamename is empty, but you have a different one here that checks for each field as empty. Use whichever is correct.
    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-06-2013)

  • #14
    New Coder
    Join Date
    Feb 2013
    Posts
    39
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Whoa whoa, you're mixing mysql and mysqli libraries here.
    Since you are using mysqli, you can just bind the variables and be done with it. No need to escape them at all. Old Pedant's function is good and clean for mysql library, but it won't be as clean with the mysqli since the mysqli resource is (rightfully at that) not global, so it would need either globalization or passing to the function (preferable). So the end result is simply a function call that will be near identical to the use of the original code. Instead of
    $gamename = mysqli_real_escape_string($link, $_POST['gamename']);
    , you'd have $gamename = clean($link, $_POST['gamename']);, which if the only purpose is to execute mysqli_real_escape_string, I'd beg question on the overall usefulness of breaking it down into a separate function.

    Statement that up instead. I've never done a statement with an on duplicate update, but I can't see why it would be any different. I'll just pull the VALUES instead:
    PHP Code:
    <?php
    // pull your connection in here
    // Gather ye' variables with a simple pull.  Walk the $_POST through a stripslashes if get_magic_quotes_gpc() is enabled:
    if (isset($_POST['gamename'], $_POST['gamecode'], /*...etc */))
    {
        
    $gamename $_POST['gamename'];
        
    $gamecode $_POST['gamecode'];
        
    // etc
        
    $tbl_name 'yourtable';
        
    $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)
            VALUES 
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE 
                gamename = IF(gamename = '', VALUES(gamename), gamename),
                region = IF (gamename = '', VALUES(region), region),
                mode = IF(gamename = '', VALUES(mode), mode),
                vmc = IF(gamename = '', VALUES(vmc), vmc),
                smb = IF(gamename = '', VALUES(smb), smb),
                hdd = IF(gamename = '', VALUES(hdd), hdd),
                usb = IF(gamename = '', VALUES(usb), usb),
                notes = IF(gamename = '', VALUES(notes), notes),
                comp = IF(gamename = '', VALUES(comp), comp)"
    ;  
        if (
    $stmt mysqli_stmt_prepare($link$sql))
        {
            
    mysqli_stmt_bind_param($stmt'ssssssssss'$gamename$gamecode$region$mode$vmc$smb$hdd$usb$notes$comp);
            
    mysqli_stmt_execute($stmt);
            
    mysqli_stmt_close($stmt);
        }
        else
        {
            
    printf("Failed to create a statement: %s" PHP_EOLmysqli_error($link));
        }
    }
    Like that. Untested, and I wrote it in procedural to match what you have, so I can't be 100% sure that I got it right :P

    I noticed as well that you have 2x different queries. I wrote this one with the assumption that it can go ahead and update so long as the gamename is empty, but you have a different one here that checks for each field as empty. Use whichever is correct.
    This is excellent.. as I have been wanting to get into prepared statements. As I understand it, the use of mysqli_real_escape_string is not needed with this method?

    Though, currently each input requires an }else{ and also I use regex to change some of the inputs. To me, it seems easier to handle each one independently and just use the mysqli_real_escape_string.

    Here is an example of how I use some of the input fields.
    PHP Code:
    if(isset($_POST['region'])) {
    $region mysqli_real_escape_string($link$_POST['region']);   
    } else {
    $region "O";
    }
    if(isset(
    $_POST['mode'])) {
    $mode implode(","$_POST['mode']);   
    } else {
    $mode " ";
    }
    if(isset(
    $_POST['vmc'])) {
    $vmc mysqli_real_escape_string($link$_POST['vmc']);   
    } else {
    $vmc "-";
    }
    if(isset(
    $_POST['comp'])) {
    $comp mysqli_real_escape_string($link$_POST['comp']);   
    } else {
    $comp "untested";

    I run gamecode input thru regex to guarantee a specific character format, and I use the output variable for the INSERT VALUES ('$cleangc'). Can I simply apply this variable to your method? Example..

    PHP Code:
            mysqli_stmt_bind_param($stmt'ssssssssss'$gamename$cleangc$region$mode$vmc$smb$hdd$usb$notes$comp);
            
    mysqli_stmt_execute($stmt);
            
    mysqli_stmt_close($stmt); 
    Or would I also need to include it as
    PHP Code:
    gamecode = IF(gamecode '$cleangc'VALUES(cleangc), gamecode
    And a question, what is 'ssssssssss' doing?

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    I have to say, this strikes me as indicative of bad DB design:
    Code:
        $tbl_name = 'yourtable'; 
        $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp) 
            VALUES  ...
    Why does the name of the table need to be a variable? Surely you don't have more than one table with the same fields??? Or did we discuss this before?
    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.


  •  
    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
    •