PHP MySQL Weird Update Problem

Question

I have a heap based table in MySQL that I am trying to update via PHP, but for some reason, the updates do not seem to be taking place.

Here is my test code:

<?php
$freepoints[] = 1;
$freepoints[] = 2;
$freepoints[] = 3;
foreach ($freepoints as $entrypoint) {
$query = "update gates set lane='{$entrypoint}' where traffic > 50 limit 50";
echo "$query\n";
mysql_query($query);
echo mysql_affected_rows()."\n";
}
?>

This outputs the following:

update gates set lane='1' where traffic > 50 limit 50
50
update gates set lane='2' where traffic > 50 limit 50
50
update gates set lane='3' where traffic > 50 limit 50
50

In the database to start with lanes 1/2/3 had 0 records and lanes 4/5/6 had 100 records. From this I am expecting all 6 lanes to now have 50 records each. However when I look lanes 4/5/6 still have 100 records and 1/2/3 still have 0 records.

When I copy the query "update gates set lane='1' where traffic > 50 limit 50" into phpMyAdmin it works absolutely fine, so any ideas why it isn't working in my PHP script when mysql_affected_rows is saying it has updated 50 records?

zerkms · Accepted Answer · 2010-03-31 09:13:52Z

1

Add ORDER BY clause
you are always rewriting with the same data. you need to specify WHERE with lane to update another rows

answered Mar 31, 2010 at 9:13

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

3 Comments

Tim Over a year ago

I changed query to the following but still doesn't work: update gates set lane='{$entrypoint}' where traffic > 50 and lane != '{$entrypoint}' order by lane limit 50

Tim Over a year ago

Oh now I feel such an idiot when I realise the issue you're saying. I changed query as follows and it works fine now: "update gates set lane='{$entrypoint}' where traffic > 50 and lane > '{$entrypoint}' limit 50". Thank you!

zerkms Over a year ago

even it's work for you now - i advice to always specify ORDER to get obvious results.

Collectives™ on Stack Overflow

PHP MySQL Weird Update Problem

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related