SQL updating only the last row

Question

I am having problem updating multiple rows in the database. This is my form.

<form action="results-action" method="post" enctype="multipart/form-data">
<fieldset>   

<table id ="table_id" class="display">

<thead>

<tr><td><h2>Pending Order</h2></td></tr>

<tr>

<th scope="col">Order ID</th>
<th scope="col">Name</th>
<th scope="col">Address</th>
<th scope="col">Product Name</th>
<th scope="col">Produt Quantity</th>
<th scope="col">Price</th>
<th scope="col">Order status</th>
</tr>
</thead>

<tbody>

<?php 

while ($row = mysqli_fetch_array($result)) {
?>

<tr>

<td><input type="text" value='<?=$row['virtuemart_order_id']?>' name="orderid" id="virtuemart_order_id"></td>

<td><?=$row['first_name']?></td>
<td><?=$row['address_1']?></td>
<td><?=$row['order_item_name']?></td>
<td><?=$row['product_quantity']?></td>
<td><?=$row['product_final_price'] ?></td>

<td><select name='change'>

<option value='P'> Pending</option>

<option value='C'> Confirmed</option></select></td>

</tr>

<?php

}

?>

</tbody>
</table>
</fieldset>

<fieldset>
<table>
<tr>
<td><input type="submit" value="Update status" name="update status"> </td>
</tr>
</table>
</fieldset>

</form>

When I change the order status to confirm on each of the rows, it should update the order status to 'P' for each row. But instead, it updates only the latest row.

This is my update code.

$change = $_POST["change"];
$orderid = $_POST['orderid'];

// build SQL statement 
$query = "update ruj3d_virtuemart_order_items set order_status= '$change' WHERE virtuemart_order_id = '$orderid'";

// execute SQL statement
$status = mysqli_query($link, $query) or die(mysqli_error($link));

if ($status) {
echo "<big>It has been updated !!. </big>";

}else {
echo "<br>Has not been successfully confirmed</br>";
}

Answer 1

Change your dropdown as below

<select name='change[<?=$row['virtuemart_order_id']?>]'>
  <option value='P'> Pending</option>
  <option value='C'> Confirmed</option>
</select>

Then in your php,

foreach($_POST['change'] as $orderid => $change) {
   $query = "update ruj3d_virtuemart_order_items set order_status= '$change' WHERE virtuemart_order_id = '$orderid'";

   // execute SQL statement
   $status = mysqli_query($link, $query) or die(mysqli_error($link));
}