Update int in MySQL Field

Question

How can I increment an int in a cell of a MySQL database? I know that auto-increment is no use because I never want to add a new row, just update an existing one. I'm currently using this (POST var used for clarify, is verified in the real code):

$columnToUpdate = 'type'.$_POST['voteid'];
$query = "UPDATE myTable $columnToUpdate = $columnToUpdate+1 WHERE id=1;";
if(!mysql_query($query)) {
    echo json_encode(array('success' => false, 'message' => 'Update failed: '.mysql_error()));
    exit;
}

In the database I have 6 fields, id, type1, type2, type3, type4, type5, and a single row with id set to 1. The intention is to recieve a number (1-5), and build a reference to the correct column before updating the field. That results in Update failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=type4+1 WHERE id=1' at line 1, so I guess it's not getting the field value out properly before it increments.

Once this is working I'm also going to need to decrement a field in the same way, unless its value is 0. So for bonus points, can I do all this in one query or would it be better to split it up?

Like I said above, this is simplified to get to the root of the problem. Of course I'm not just going throw POST data into a query :) — shanethehat, May 26 '11 at 14:50

marnir · Accepted Answer · 2011-05-26T14:51:36.323

13

I think you've missed the keyword 'SET' from your query - try

$query = "UPDATE myTable SET $columnToUpdate = $columnToUpdate+1 WHERE id=1;";

Edit:

To do the "decrement unless it's zero" you could use something like:

UPDATE myTable SET $columnToUpdate = 
CASE $columnToUpdate 
    WHEN 0 THEN 0
    ELSE $columnToUpdate - 1
END CASE
WHERE id=1;`

edited May 26 '11 at 14:51

answered May 26 '11 at 14:46

marnir

1,187
10
13

+1 correct answer. But to the OP: you also need to pay attention to @Denis's comment and deal with your SQL injection security issues. – Spudley May 26 '11 at 14:50
Well spotted sir, I seem to be going blind! Fancy taking a stab at the bonus question? – shanethehat May 26 '11 at 14:50
(I've edited my post to include a guess at it, but I'm not sure I'v egot the syntax perfect) – marnir May 26 '11 at 14:52

score 3 · Answer 2 · answered May 26 '11 at 14:49

3

For bonus points, to decrement:

$query = "UPDATE myTable SET '$columnToUpdate' = '$columnToUpdate'-1 WHERE id=1 AND '$columnToUpdate' > 0";

answered May 26 '11 at 14:49

qbert220

11,220
4
31
31

Nice one, and that will combine with the original as well. Bit lazy of me to ask, so thanks! – shanethehat May 26 '11 at 14:52

score 0 · Answer 3 · edited May 23 '17 at 10:00

Besides the injection issues, it seems as if your workflow may need some work. Are you sure you want to choose the column that will be updated based on POST variable? It seems like you would specify the column and use the variable to find the record that needs to be updated:

IE: "UPDATE myTable SET votes=votes+1 WHERE id=$post_variable;"

Again you should send the variable as a parameterized query to protect yourself from SQL injection.

Update int in MySQL Field

3 Answers3