Increment database value with php

Question

I have a model in my codeigniter application that adds a point to an entry every time a user clicks a button.

The points are stored in the "Items" table in my database as a field. points int(11). The points is set as default 0 in the database.

The code that adds a value of 1 to the current value of points in the table for an item is shown below:

public function add_point($id, $current)
    {

        $data = array(
        'points'   => $current+1,

        );


        $this->db->where('item_id', $id);
        $this->db->update('Items', $data); 
    }

This only works the first time adding a point. When I click the button, when the current point on the item is 0 (the default), it adds the point and the database shows the value changed from 0 to 1. But when I try again, and click the button, and it should add and update the value from 1 to 2, it doesn't add the point.

The code that gets the current value and passes it to my model is in my controller:

  $getq=$this->upload_model->get_item($item_id);


    foreach ($getq as $item) {
        $item['points'];
    }
$this->upload_model->add_points($item_id,$item['points']);

However it will always update from 0 to 1, and add that point only.

Not sure what I'm doing wrong. How do I fix this so that the current value in the database is incremented by 1 every time the button is clicked.

run a select query in this function and get max value of id then add maxvalue+1 in your table — Awlad Liton
– Awlad Liton, Commented Jan 22, 2014 at 4:28
@kumar_v please see above. This code gets the data for the item fine, and I use it elsewhere in my application, however, when used in the function add_points, it only update from 0 to 1 — Tester
– Tester, Commented Jan 22, 2014 at 4:31

dev7 · Accepted Answer · 2014-01-22 04:45:11Z

1

You are actually over complicating it, MySQL has built in features for that. CodeIgniter lets you run a full query as well.

Just use:

 $this->db->query("UPDATE Items SET points=points+1 WHERE item_id='".$this->db->escape($id)."'");

By using the code above, you are eliminating the need to keep track of the points in your PHP code. And it will be faster as well.

Added $this->db->escape($id) to avoid SQL injection per @Loz comment.

Hope this helps!

edited Jan 22, 2014 at 4:45

answered Jan 22, 2014 at 4:33

dev7

6,4196 gold badges36 silver badges68 bronze badges

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

4 Comments

Lawrence Cherone Over a year ago

But open to sql injection.

dev7 Over a year ago

Only if the $id comes directly from an input. From his answer I understand the $id is handled internally.

Tester Over a year ago

@Yani This worked, thanks. Also, the id come from the input post from a form. However,in some cases in the app I pass the id in the url and use uri segment to get the value

dev7 Over a year ago

Therefore you need to escape the $id to avoid SQL injection (in its simplest form someone can enter in the form something like ' OR 1=1'...that would mess up your db). I'll revise the answer.

Drace · Accepted Answer · 2014-01-22 04:35:10Z

0

You can do like the other suggested and get the $current value so you can add +1 to it, or simply write out the SQL yourself, codeIgniter allows it:

public function add_point($id)
{
    $sql = 'UPDATE items set points=points+1 where item_id='.$id;
    $this->db->query($sql);
}

This will take the current value of points where the item_id is equal to your $id and add 1 to it.

Best of luck!

PS: you don't even need the $current

answered Jan 22, 2014 at 4:35

Drace

69910 silver badges23 bronze badges

Comments

Shivam · Accepted Answer · 2014-01-22 04:50:11Z

0

You are calling your function wrongly whenever you are passing parameter $item['points'], It always start with 0 only you should not call function like this.

answered Jan 22, 2014 at 4:50

Shivam

7303 gold badges10 silver badges25 bronze badges

Comments

Paresh Thummar · Accepted Answer · 2014-01-22 04:43:22Z

-1

Safe way to execute such query

$qry = $this->db->escape_str("UPDATE Items SET points=points+1 WHERE item_id='.$id.'");
$status = $this->db->simple_query($qry);

answered Jan 22, 2014 at 4:43

Paresh Thummar

9287 silver badges20 bronze badges

Collectives™ on Stack Overflow

Increment database value with php

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related