CODEWITHSUNDEEP
phpmysqlincrement
shanethehat
shanethehat

Reputation: 15570

Update int in MySQL Field

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?

Upvotes: 2

Views: 12129

Answers (3)

garnertb
garnertb

Reputation: 9584

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.

Upvotes: 0

marnir
marnir

Reputation: 1237

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;`

Upvotes: 13

qbert220
qbert220

Reputation: 11556

For bonus points, to decrement:

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

Upvotes: 3

Related Questions