Codeigniter, increase database value by value contained in variable [duplicate]

Question

I am using codeigniter, and I have the following function in my model to give points to the user. It is however not working, rather setting the points column to 0.

This is how it is written in the codeigniter manual. Therefore I have no clue why it is not working...

Thanks

function give_points($username,$points)
{
    $this->db->set('points', 'points + $points');
    $this->db->where('username', $username);
    $this->db->update('users'); 
    echo"done";
}

Dharman · Accepted Answer · 2023-02-02 12:07:36Z

15

You have to tell CI specifically to not escape the text. Something like:

$this->db->set('points', 'points + ' . (int) $points, FALSE);

edited Feb 2, 2023 at 12:07

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jul 31, 2011 at 14:24

Sid

2,1761 gold badge11 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pascal MARTIN · Accepted Answer · 2011-07-31 13:53:20Z

1

Not sure this is the cause of your problem, but you are using single quotes, on the following lines :

$this->db->set('points', 'points + $points');

With this, the $points string will be injected as-is, literally, into your SQL query -- it's not its value that's going to be used.

If you want $points to be interpolated (so its value is put in its place, in that string), you must use double quotes :

$this->db->set('points', "points + $points");

For more informations about variable interpolation, see the Variables parsing section of the PHP Manual.

answered Jul 31, 2011 at 13:53

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

2 Comments

Thomas Clowes Over a year ago

That producse the query: UPDATE users SET points = 'points + 1000' WHERE username = 'thomas' which does not work.. any further ideas?

Pascal MARTIN Over a year ago

Looks like your points field in the database is an integer, and you are trying to access it like a string : your query should look like UPDATE users SET points = 'points + 1000' WHERE username = 'thomas' ;;; Maybe you have some way of indicating that to CI ? I don't know CI well enough to help more, sorry.

Shi · Accepted Answer · 2011-07-31 13:55:38Z

-1

If there is a chance, always check the created SQL query - I do not know how to do it with CI.

However, your set() looks flawed.

$this->db->set('points', "points + $points");

Previously, $points was part of the string, and not expanded by the contents of $points, due to you using a single quote instead of a double quote - see the manual regarding strings in PHP.

$this->db->set('points', 'points + ' . (int) $points);

A slighty better code is the one above, as it defeats possible SQL injection, depending on where $points originally comes from.

answered Jul 31, 2011 at 13:55

Shi

4,2581 gold badge28 silver badges31 bronze badges

2 Comments

Thomas Clowes Over a year ago

Both suggestions produce the query: UPDATE users SET points = 'points + 1000' WHERE username = 'thomas' This query is not working... Thanks

Shi Over a year ago

You have to check the CI manual then. It seems as if CI always puts the given string in set() into quotes, so this cannot work that way.

Collectives™ on Stack Overflow

Codeigniter, increase database value by value contained in variable [duplicate]

3 Answers 3

Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Linked

Related