[RESOLVED] Active Records & Arithmetic updates...

[eluser]Clooner[/eluser]
This is the first time I have to use arithmetic sql using Active Records but I run into trouble...

Code:

function deduct_balance($id,$amount)
{
    $data = array('balance' => "balance-$amount" );
    $this->db->where('id', $id);
    $this->db->update($this->table, $data);
}

This generates

Code:

UPDATE `users` SET `balance` = 'balance-1000' WHERE `id` = '1'

but it want it to generate

Code:

UPDATE `users` SET `balance` = `balance`-'1000' WHERE `id` = '1'

When using select you can disable the parentheses but update doesn't seem to have that functionality. How do I use active records to generate the correct query?

[eluser]Christopher Clarke[/eluser]
I know this works with $this->db->where, so it might work with set as well:

$this->db->set('`balance` = (`balance` - '.$amount.')');

You'll need to remove $data from $this->db->update.

I haven't tested the above, but similer code works with where.

Like I have the following and it works:

$this->db->where( "(`story_primary_genre` = " . $this->db->escape($genre) . " or `story_secondary_genre` = " . $this->db->escape($genre) . ")" );

Good luck.

[eluser]Clooner[/eluser]
Hum. That is the answer

I was focused on using update and I never looked any further.

I can use

Code:

$this->db->set();

and give a parameter so it won't escape.

Thanks for pointing that out

This is how the function is now

Code:

function deduct_balance($id,$amount)
{
    $this->db->set('balance' , "`balance`-$amount",false);
    $this->db->where('id', $id);
    $this->db->update($this->table);
}