0

How to use update query for multiple values in a single shot with prepare statement in a single shot. I have an array with name $popularpackages and i want to use prepare statement to update table records in a single shot

  $packageid= mysqli_real_escape_string($conn, $_POST['packageid']);
  $maxcount = mysqli_real_escape_string($conn, $_POST['loop']);
  for($loop = 0; $loop < $maxcount; $loop++){
           $packageid = $packageid[$loop];
   }
 $sql = $conn->prepare("update tbl_packagedetails set popularpackages =? where packageid =?;");
 $sql->bind_param("ii",popularpackages, $packageid);
 $sql->execute();

$popularpackages contains (id, value) as (1,0),(2,0),(3,1),....(10,0)

Nandani
  • 21
  • 3
  • 1
    short answer you can't. You have to do it in a loop – Jens Jan 04 '19 at 12:27
  • You can do it using https://stackoverflow.com/questions/3432/multiple-updates-in-mysql, but I would stick with a loop as it's more maintainable. – Nigel Ren Jan 04 '19 at 12:37

1 Answers1

0

You'll have to loop it. But you should begin transaction before, run the loop and executing and only when the loop finishes you should commit. This way you will only have one transaction instead of multiple which is what I think you want to achieve.

$dbh->beginTransaction();

<loop while executing>

$dbh->commit();
Carlos Alves Jorge
  • 1,919
  • 1
  • 13
  • 29