DBAPI Update - Incrementing a field value possible?

Is it possible to increase a field value with $modx->db->update

Here is how I try to do it -

I want to increase the field2 value by one, (field2 is an int field )

Fields array is defined as follows

$fields = array(’field1’ => ’field1 value’,
’field2’ => ’field2+1’
);
and actual call

$result= $modx->db->update($fields,$tbTable,$whereSql );


No error is returned or logged in error log but nothing also happens and value is not increased.

$result also returns a "0" zero value.


I tried different versions for ’field2+1’ like

field2+1
’field2’+1

even if you give a wrong field name for field2, no error returned.

Is this update possible with DBAPI or should I use direct mysql object for this process?

Thank you

That should work;

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The API function returns this query

UPDATE myTable SET field1=’field1 value’,field2=’field2+1’ WHERE ’condition’

I would imagine that the problem is in the condition you set. Try running the full query you expect the API is generating in a phpMyAdmin sql field and see what happens.

Is field2 a unique key? In that case, you may have to change the order of execution

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

Thanks Sottwell.

the field2 is not a unique key.

I tried update statement from phpmyadmin, the following works (field2=field2+1)

UPDATE myTable SET field1=’field1 value’,field2=field2+1 WHERE ’condition’

but this doesn’t work (field2=’field2+1’)

UPDATE myTable SET field1=’field1 value’,field2=’field2+1’ WHERE ’condition’

this actually sets the field2 back to zero (same thing happens when I run $modx->db->update)

it looks the problem is with single quotation marks, and looking at the function source code, I don’t see any workaround.

Anyway, I used the $modx->db->query api call instead, in case anyone else encounter same problem.