Welcome Guest, Not a member yet? Register   Sign In
Active Record: add +1 to current value (update)
#1

[eluser]Razican[/eluser]
Hello, I'm getting crazy with this thing. I have a table where one value in one column should be updated t locck like current value +1 (or other number). The correct query would be something like this:
Code:
UPDATE `database`.`market` SET `amount` = `amount` +1 WHERE `market`.`id` = 1;

I have tried to do it via Active record and even with $this->db->query(); but with active record it crashes saying this in the error log:

Quote:DEBUG - 06-04-2011 19:20:26 --> DB Transaction Failure
ERROR - 06-04-2011 19:20:26 --> Query error: Incorrect integer value: '`amount` -4' for column 'amount' at row 1

And using $this->db->query(); I do not get the expected result as it puts the integer -4 in an unsigned bigint(20) field (18446744073709551611). What can I do?

Note: The crash report is done adding -4 value to current (120).
#2

[eluser]LuckyFella73[/eluser]
Your active record code should look like this:
Code:
$this->db->set('amount', 'amount+1',FALSE);
$this->db->where('id', 1); // '1' test value here ?
$this->db->update('market');

I use this code (changed for your needs) to update
position columns in my tables and it works for me.

EDIT: don't know if you can do this with negative values
(positions are usually in the positive range ..)

EDIT: I did set up a test-table and checked that - now
I know it is possible with negative Values too Wink
#3

[eluser]Razican[/eluser]
I didn't saw the $this->db->set() method, I was using $this->db->update() instead. Thanks it's working now.
#4

[eluser]wyl_g[/eluser]
i am having some trouble with this code. it keeps writing 1 in my database and if i remove the +1 it doesn't change/update the password. and if i remove the +1 and false params, i get a 1054 error. please help



function set_password($id, $password){

$this->db->set('password', $password +1, false);
$this->db->where('id', $id);
$this->db->update('workers');

if ($this->db->affected_rows() == '1')
{
return true;
}
return false;

}//end set password
#5

[eluser]cahva[/eluser]
Ehm.. If its a password, why do you want to increment that by one? Usually passwords are not numeric entirely and if your $password variable contains letters, PHP will treat is as zero when you add +1 to it and thats why it is 1 when you update it.
#6

[eluser]wyl_g[/eluser]
i am just trying to update a password n its just not working. so i tried all the ideas listed above but still nothing. i saw what u are saying but i tried it anyway.
#7

[eluser]cahva[/eluser]
If you're trying to update the password with a new one, then you should do it like you normally update a record:
Code:
$this->db->set('password', $password);
$this->db->where('id', $id);
$this->db->update('workers');

..or
Code:
$arr = array(
    'password' => $password
);
$this->db->where('id', $id);
$this->db->update('workers',$arr);

The initial problem for this thread is that the user wanted to update the existing record incrementing it by one. The third parameter false will tell CI not to escape the generated SQL and therefore you can do somefield=somefield+1. Without the third parameter set to false, CI would create the sql like: somefield='somefield + 1' which is not the same.




Theme © iAndrew 2016 - Forum software by © MyBB