Updating rows in mysql with a foreach loop

Question

Just started learning php and this is my first project: sort list items and save that new order to database. But I'm really stuck on how to save the order with the new order numbers.

I have an array like this: order numbers

and I want to loop through this and update the ordernumber column in my table.

so far I have this set up:

foreach ($a as $key => $neworder) {
    $sql = "UPDATE todoitem SET ordernumber = '$key' WHERE Id = '$neworder'";
}

But when I sort the list items, I get this: todoitem table

Only the last row gets updated and the order number is 3? I don't understand where the 3 come from.

I've been scratching my head at this for a few days and I'm so stuck...

The above statement only shows the query - where is actually **executed** ? Perhaps outside the loop, this would explain why only the *last* item got updated. Also, please update your question and show the text rather than providing images. — Jan, Feb 08 '16 at 18:42
Take a look at [this](http://stackoverflow.com/questions/8893551/update-query-php-mysql) — Peyman Mohamadpour, Feb 08 '16 at 19:11

score 2 · Accepted Answer · answered Feb 08 '16 at 18:48

This:

foreach ($a as $key => $neworder) {

is looping from 0-3 (since there are 4 keys in your array by those numbers).

This:

$sql = "UPDATE todoitem SET ordernumber = '$key' WHERE Id = '$neworder'";

Is setting the $sql variable to a string, built by the $key and $neworder values.

Now, the code never actually does anything with that query inside that loop. It just over-writes it each time. So the last iteration of the loop will be the value of $sql after the loop. (Presumably that's where you're actually using it in some way.)

If my assumption is correct and you are executing the query after the loop, then the code is semantically doing the following:

Set the query to use 0.
Set the query to use 1.
Set the query to use 2.
Set the query to use 3.
Execute the query.

It's only executed once. Maybe you meant to execute the query inside of the loop?

Thanks! I was dumb and didn't put the execution code inside the loop... Much more to learn! — itsybitsy, Feb 08 '16 at 19:06

score 0 · Answer 2 · answered Feb 08 '16 at 18:45

You are overwriting your $sql variable in the loop. You should also execute it inside the loop. This way only the last $sql gets executed.

As for the line with id 4 having ordernumber 3, you might want to change your sql parameters - switch $key with $neworder. That is

"UPDATE todoitem SET ordernumber = '$neworder' WHERE Id = '$key'";

Updating rows in mysql with a foreach loop

2 Answers2