CODEWITHSUNDEEP
phpmysqli
user3268051
user3268051

Reputation: 23

How can I update multiple rows in MySQL using PHP and the mysqli_query() command by passing dynamic variables?

I'm trying to update these rows to remove the items in my cart after someone purchases them. The problem is I only get the first set changed to the quantity I want. How can I make this work? If I echo out the $update variable, it shows me an UPDATE query for each item. I just can't get the sql3 var to repeat.

$id_str_array = ['44-1','43-2']    

foreach ($id_str_array as $key => $value) {

  $id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate   product ID from its quantity
  $product_id = $id_quantity_pair[0]; // Get the product ID
  $product_quantity = $id_quantity_pair[1]; // Get the quantity
  $sql2 = mysqli_query($con, "SELECT * FROM INVENTORY WHERE ID = $product_id");
  while($row2 = mysqli_fetch_array($sql2)){
    $p_stock = ($row2['PRODUCT_STOCK'] - $product_quantity);
    $update .= "UPDATE INVENTORY SET PRODUCT_STOCK= $p_stock WHERE ID= $product_id;";
  }
  $sql3 = mysqli_query($con, $update);
}

Shouldn't the mysqli_query function get run once for every value in the foreach loop any help would be greatly appreciated

Upvotes: 2

Views: 4771

Answers (3)

Phil
Phil

Reputation: 164809

This is the perfect opportunity to make use of a prepared statement. These are optimised to be run multiple times with different values. For example...

$stmt = $con->prepare('UPDATE INVENTORY SET PRODUCT_STOCK = (PRODUCT_STOCK - ?) WHERE ID = ?');
if (!$stmt) {
    throw new Exception($con->error, $con->errno);
}
$stmt->bind_param('ii', $product_quantity, $product_id);

foreach ($id_str_array as $value) {
    list($product_id, $product_quantity) = explode('-', $value);

    if (!$stmt->execute()) {
        throw new Exception($stmt->error, $stmt->errno);
    }
}

Upvotes: 3

Aljie
Aljie

Reputation: 190

Try this:

$id_str_array = ['44-1','43-2']    

 foreach ($id_str_array as $key => $value) {

  $id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate   product ID from its quantity
  $product_id = $id_quantity_pair[0]; // Get the product ID
  $product_quantity = $id_quantity_pair[1]; // Get the quantity

  $sql2 = mysqli_query($con, "SELECT * FROM INVENTORY WHERE ID = $product_id");
  $x = 1;
  while($row2 = mysqli_fetch_array($sql2))
  {
   $p_stock[$x] = ($row2['PRODUCT_STOCK'] - $product_quantity);
   $x++;
  }
  for($x=1;$x <count($p_stock[$x]); $x++)
  {
     $update = "UPDATE INVENTORY SET PRODUCT_STOCK= '$p_stock' WHERE ID= '$product_id[$x]' ";
     $sql3 = mysqli_query($con, $update);
  }
 }

Hopes this helps ..

Upvotes: -2

dave
dave

Reputation: 64657

Try this:

$id_str_array = ['44-1','43-2']    
$update = "";
foreach ($id_str_array as $key => $value) {
  $id_quantity_pair = explode("-", $value);
  $product_id = $id_quantity_pair[0]; // Get the product ID
  $product_quantity = $id_quantity_pair[1]; // Get the quantity
  $update .= "UPDATE INVENTORY SET PRODUCT_STOCK=PRODUCT_STOCK - "
           . $product_quantity . " WHERE ID= $product_id; ";
}
$sql3 = mysqli_multi_query($con, $update);

Upvotes: 0

Related Questions