+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Incrementing DB values (/showthread.php?tid=77972)
Incrementing DB values - Derek - 11-12-2020
Is there a way in CI4 to increment a value in a database in one query?
I would like to be able to do something like:
Code:
UPDATE table set field = field+5 where some_conditionI know I can do this:
PHP Code:
$db->query('UPDATE table set field = field+5 where some_condition');
I am just wondering if there is a way to do it without having to write the query myself.
RE: Incrementing DB values - InsiteFX - 11-13-2020
With QueryBuilder this should work, not tested but give it a try.
PHP Code:
$db = db_connect();
$builder = $db->table('your_table');
$data = ['votes' => '(votes + 5)'];
// or
$votes = $votes + 5;
$data = ['votes' => $votes];
$builder->where('id', $id)
->update($data);
RE: Incrementing DB values - wdeda - 11-13-2020
I am not a professional and, therefore, sometimes when I need to execute something related to DBs that is not within my knowledge, I first try to find the solution in phpMyAdmin, in most cases I have the answer I need, remembering that every action performed has the code displayed.
RE: Incrementing DB values - Derek - 05-14-2021
I was finally able to get around to the use case where I needed to do this and here is what worked for me.
PHP Code:
$this->set('field', 'field + ' . $this->db->escape($amount]), false)
->where('id', $someId)
->update();
The key here is that you have to use the set method and not pass the data into the update function. This allows you to use the third parameter to specify that you don't want it to escape the values. This does mean, however, that you have to escape any variable yourself as shown in this example.
I found this in the documentation at https://www.codeigniter.com/user_guide/database/query_builder.html#updating-data under the information on the $builder->set() method.