PHP MySQL UPDATE statement succeeds but column is not updated

Question

Would appreciate the advise of SO's php/MySQL experts! I am stumped by the following seemingly simple piece of php code.

$queryDel = 'UPDATE `cocoon_result` SET `image` = NULL WHERE `id` = "B" AND `post_id` = 183';

$resultDel = mysqli_query($mysqli, $queryDel);
if (!$resultDel)
    $msg .= 'Errormessage: ' . mysqli_error($mysqli) . '<br />';
else if (mysqli_affected_rows($mysqli) == 0)
    $msg .= 'Errormessage: ' . mysqli_error($mysqli) . '<br />';
else
    $msg .= mysqli_affected_rows($mysqli) 'row(s) affected. What the ??';

The statement succeeds and says that 1 row is affected. However image is still the old value. When I entered the same sql statement via phpMyAdmin, the update works. I have tried updating image to '' and '123' and the error persists so it's not just when image = NULL.

The PRIMARY KEY for table cocoon_result is id and post_id.

Edit: I've transplanted this snippet of code into a new file and it works... it just doesn't work together with the other lines of code in the original file. I've incrementally added back the other parts of the code to this new file and it still works but it's going to be quite crazy for me to add lines of code bit by bit till the original file is reproduced... Plus I'm darn curious as to the reason for this strange behaviour. Anyone?

Since update is "successful" and 1 row is affected, I don't understand what other reasons there could be for ths error... Any ideas? — user3585550
– user3585550, Commented Apr 29, 2014 at 15:22

Al.G. · Accepted Answer · 2014-04-29 14:33:24Z

3

I think your code structire is wrong. Your code should be like this:

$queryDel = 'UPDATE `cocoon_result` SET `image` = NULL WHERE `id` = "B" AND `post_id` = 183';

$resultDel = mysqli_query($mysqli, $queryDel);
$affected_rows = mysqli_affected_rows($mysqli);

if ($affected_rows === -1) //if the query has failed, displaying the error
    $msg .= 'Error deleting result list image: ', mysqli_error($mysqli);
else if (mysqli_affected_rows($mysqli) == 0) //if the query hasn't returned any rows
    $msg .= 'No rows affected<br />';
else
    $msg .= mysqli_affected_rows($mysqli) 'row(s) affected. What the ??';

edited Apr 29, 2014 at 14:33

answered Apr 29, 2014 at 14:18

Al.G.

4,4476 gold badges34 silver badges63 bronze badges

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

2 Comments

user3585550 Over a year ago

The value of mysqli_affected_rows($mysqli) here is 1.

user3585550 Over a year ago

that's not it - $affected_rows = 1

Collectives™ on Stack Overflow

PHP MySQL UPDATE statement succeeds but column is not updated

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related