HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

How do Increment value in mysql update query when the column is a variable?? can it be done?

peregrineperegrine MVP
edited July 2013 in Feedback

I can do this
$BlahID = "3";
$MyColumn = "ColumnA";

so the value in MyColumn is e.g. "5" it read the 5 and increments the number in the column, so it is now 6.

Gdn::SQL()
->Update('MyTable')
->Set(array($MyColumn => ColumnA + 1))
->Where(array('MyID'=>$BlahID))
->Put();


However, how does one do this when it is a variable.

Gdn::SQL()
->Update('MyTable')
->Set(array($MyColumn => $MyColumn + 1))
->Where(array('MyID'=>$BlahID))
->Put();

without resorting to a sql select and then an update. (2 database actions) vs one.

I'd like somehow to have the parse pull the value out of the $MyColumn column (and append one to the value) in table MyTable and store that incremented number in in the $MyColumn column.

without it thinking $MyColumn is a string +1 , but rather read value from $MyColumn in table and then + 1.

I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Comments

  • businessdadbusinessdad Stealth contributor MVP
    edited July 2013

    Maybe it's a stupid question, but why not just running a simple, plain SQL statement?

    $SQL = "
      UPDATE GDN_SomeTable
      SET SomeColumn = SomeColumn + 1
      WHERE MyID = $BlahID"; // Obviously, $BlahID should be sanitised before used
    Gdn::Database()->Query($SQL);
    
  • Either I don't understand or possibly I didn't explain well enough.
    the problem is the variable in bold (I want the column to be a variable on the right hand side of the set.
    How can it be interpereted to increment column value instead of adding 1 to a string.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • x00x00 MVP
    edited July 2013

    good question you do it like so

    ->Set('Column', 'Column + 1', FALSE)

    note the third parameter, that tells it NOT to escape the string.

    grep is your friend.

  • peregrineperegrine MVP
    edited July 2013

    thanks for the answers x00 and businessDad but ..

    but I want $Column not Column (I'm not sure if you read my intent correctly :)

    I had tried false before.

    the column variable can be a series of columns that will be updated depending on other circumstances.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • peregrineperegrine MVP
    edited July 2013

    I also tried

    $Set1 = "'" . $AddColumn . "'=>'" . $AddColumn . " + 1'";
    echo $Set1;
    Gdn::SQL()
    ->Update('Table')
    ->Set(array($Set1))
    ->Where(array('MyColID'=>$MyID))
    ->Put();

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • peregrineperegrine MVP
    edited July 2013

    and this too

    $AddColumn = "MyColumn";

    $Set1 = "'" . $AddColumn . "','" . $AddColumn . " + 1',FALSE";
    echo $Set1;

    which parses out to
    'MyColumn','MyColumn + 1',FALSE

    Gdn::SQL()
    ->Update('Table')
    ->Set($Set1)
    ->Where(array('MyColID'=>$MyID))
    ->Put();

    all result in PDO errors.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • businessdadbusinessdad Stealth contributor MVP

    Ok, I think I got it now. I think you can either do like this:

    $SQL = "
      UPDATE GDN_SomeTable
      SET $ColumnVar= $ColumnVar + 1
      WHERE MyID = $BlahID"; // Obviously, $BlahID should be sanitised before used
    Gdn::Database()->Query($SQL);
    

    Or, as @x00 wrote, like this:

    ->Set($ColumnVar, "$ColumnVar + 1", FALSE)
    

    Note the double quotes in both cases, which will ensure that variable $ColumnVar is parsed and replaced with its value.

  • peregrineperegrine MVP
    edited July 2013

    @businessdad said:
    Ok, I think I got it now. I think you can either do like this:

    $SQL = "
      UPDATE GDN_SomeTable
      SET $ColumnVar= $ColumnVar + 1
      WHERE MyID = $BlahID"; // Obviously, $BlahID should be sanitised before used
    Gdn::Database()->Query($SQL);
    

    Or, as x00 wrote, like this:

    ->Set($ColumnVar, "$ColumnVar + 1", FALSE)
    

    Note the double quotes in both cases, which will ensure that variable $ColumnVar is parsed and replaced with its value.

    BINGO!

    I got both working techniques thanks much. @businessdad and @x00 :)

    @businessdad - thanks for the proof of concept and the ultimate answer. I was beginning to wonder.

    I'd probably be at this for a long long time ..... i was trying AS and Set @Tempvar and strings, thinking it couldn't be done with the set () syntax.

    I know i tried it before, because i tried every permutation known to mankind. Must of gone to the wrong set of sql statements or I had another table lookup that blocked the update.

    Bonus points for this answer:

    One thing I've been wondering for awhile...

    Is there a way to put several set statements and set the appropriate ones.

    $SQL = "
    

    UPDATE GDN_SomeTable
    Set(column1, 'id', FALSE ) if $x = $y
    Set(column2, 'id', FALSE) if $x = $D
    WHERE MyID = $BlahID";
    Gdn::Database()->Query($SQL);

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • businessdadbusinessdad Stealth contributor MVP

    @peregrine said:
    Is there a way to put several set statements and set the appropriate ones.

    Computer says no... Actually, I'm not sure I understood the question.

  • peregrineperegrine MVP
    edited July 2013

    businessdad said:

    Actually, I'm not sure I understood the question.

    A while back I was doing something that I can't reall what and where :).

    But lets say you want to update certain columns in a table. Each column update based on whether something meets a certain criteria. All in a "single sql" update using vanilla lingo.

    If $a = "Yes" update column 1 with something
    e.g.
    ->Set($ColumnVar, "$ColumnVar + 1", FALSE)

    and if $a= "Yes" and $b = "Yes" update the column "$this->Column"
    ->Set($this->Column, '', FALSE)

    So if $a was no there would be no update.

    if $a was "Yes" and $b was "No" $ColumnVar would be incremented by one

    if $a was "Yes" and $b was "Yes"
    $ColumnVar would be incremented by one
    $this->Column, would have its value set to "";

    The question is where would the "if statement" go in the scheme of things.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • The benefit of the query builder is you can break it up.

    $SQLStatement = Gdn::SQL();
    $SQLStatement->Update('Table');
    
    if(condition){
         $SQLStatement->Set('MyColumn','MyColumn + 1',FALSE)
    }else{
         $SQLStatement->Set('MyColumn1','MyColumn1 + 1',FALSE)
    }
    
    $SQLStatement->Where(array('MyColID'=>$MyID))->Put();
    

    alternatively you an supply an array

    if(condition){
         $SetStatement = array('MyColumn','MyColumn + 1');
    }else{
         $SetStatement = array('MyColumn1','MyColumn1 + 1');
    }
    
    Gdn::SQL()
    ->Update('Table')
    ->Set($SetStatement, NULL, FALSE)
    ->Where(array('MyColID'=>$MyID))
    ->Put();
    

    So if $a was no there would be no update.

    Then there would be no point executing the statement.

    $this->Column, would have its value set to "";

    not sure I follow.

    grep is your friend.

  • thx @x00

    that explains it.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Sign In or Register to comment.