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

peregrine · July 2013

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.

businessdad · 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);

peregrine · July 2013

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.

x00 · 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.

peregrine · 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.

peregrine · July 2013

I also tried

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

peregrine · 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.

businessdad · July 2013

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.

peregrine · 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);

businessdad · July 2013

@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.

peregrine · 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.

x00 · July 2013

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.

peregrine · July 2013

thx @x00

that explains it.

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

Comments