CODEWITHSUNDEEP
phpmysql
itsybitsy
itsybitsy

Reputation: 13

Updating rows in mysql with a foreach loop

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...

Upvotes: 0

Views: 5021

Answers (2)

David
David

Reputation: 218837

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?

Upvotes: 2

radoh
radoh

Reputation: 4809

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'";

Upvotes: 0

Related Questions