CODEWITHSUNDEEP
phpmysqlforeach
Oscar
Oscar

Reputation: 135

How to use foreach to update value in Database iteratively?

I am working for a database as below and trying to update all the role rows when click update button. How do I use the foreach method correctly for the update SQL commend if I want to update all 1 to 2? Do I need to push all the post value to an array then using foreach method. Is the following code correctly to set the SQL commend? Thanks!

foreach() {
  $sql = "UPDATE user_table set role = ? WHERE id = ?";
  $stmt = $conn->prepare($sql);
  $stmt->bind_param('ii', $role, $id);
  $result = $stmt->execute();
}

enter image description here

Upvotes: 0

Views: 311

Answers (3)

Mike Robinson
Mike Robinson

Reputation: 8945

Also a few points about your code as provided:

  • Prepare the statement once, outside the loop. Then, repeatedly execute it, binding new parameter values each time.

  • Use transactions. BEGIN TRANSACTION before you start updating, then COMMIT upon completion. Or, ROLLBACK if anything goes wrong (e.g. in a "try...catch" block). This will make the operation atomic as seen by all other database users – they will see all the updates occurring at the same instant, or they will see that nothing happened at all. (On many database systems, this also makes the operation considerably faster and more efficient.)

Upvotes: 0

ronak-ah
ronak-ah

Reputation: 26

From what I understand, you want to update 'role' column and change the value of '1' to '2'.

Best way to proceed would be to rely on SQL entirely.

Try This without any loops

$role_to_change = 1;
$new_role = 2;
$sql = "UPDATE user_table set role = ? WHERE role = ?";
$stmt = $conn->prepare($sql);
$result = $stmt->execute([$role_to_change, $new_role]);

Upvotes: 0

Barmar
Barmar

Reputation: 780889

You don't need to use foreach. You can do it entirely in SQL:

UPDATE user_table SET role = 2 WHERE role = 1;

Upvotes: 2

Related Questions