CODEWITHSUNDEEP
phpmysqlmysqli
Bagwell
Bagwell

Reputation: 2226

Addition works, subtraction doesn't

I have the following query:

$stmt = $cxn->prepare("UPDATE comments SET counter = counter + 1 WHERE ? LIKE concat(path, '%')");
$stmt->bind_param('s', $path);
$stmt->execute();

Where $path is something like 1/2/3/.

This query increments the counter fine for each result, but if I change the query to subtract (counter = counter - 1), it won't actually subtract and I'm not sure why. After all, I'm only changing that one operator.

What's wrong?

Upvotes: 0

Views: 150

Answers (2)

Bagwell
Bagwell

Reputation: 2226

The solution to this problem was that, because I was using unsigned integers for the counter, if the counter was already set to 0, the query wouldn't work.

So either you have to check that counter doesn't equal 0 or make it signed.

Upvotes: 0

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

Do it this way, which is the method I use:

SET `counter` = (`counter` - 1)

and another method:

SET counter = (counter-1)

EDIT:

your WHERE clause should be more precise.

I.e.: WHERE column=column_to_change instead of just WHERE ?

Here's my working piece of code:

UPDATE `mytable` SET `fieldname` = (`fieldname` - 1) WHERE `some_id` = 1

Upvotes: 1

Related Questions