I want to fetch array data from “orders” Table WHERE order_id =2 and insert into “inventory_log” Table. but my code is only inserting the last row
orders TABLE
order_id product_id quantity
2 1 9
2 3 2
3 6 3
3 5 2
2 7 1
I want this:
inventory_log TABLE
id product_id quantity
1 1 9
2 3 2
3 7 1
Find my code below:
<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'store');
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Insert into inventory_log TABLE
if(isset($_POST['Submit']))
{
$productID=$_POST['product_id'];
$IssueQty=$_POST['quantity'];
$inventory=mysqli_query($con,"insert into inventory_log(productID, IssueQty) values('$productID', '$IssueQty')");
echo '<script>alert("Sucessful")</script>';
}
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title></title>
</head>
<body>
<form method="post" name="submit">
<table border="1">
<thead>
<tr>
<th>Product ID</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
<?php
// Get order items from orders TABLE
$result = $con->query("SELECT order_id, product_id, quantity
FROM orders
WHERE order_id = 2");
if($result->num_rows > 0){
while($item = $result->fetch_assoc()){
$product_id = $item["product_id"];
$quantity = $item["quantity"];
?>
<tr>
<td><input type="text" name="product_id" value="<?php echo $product_id; ?>"></td>
<td><input type="text" name="quantity" value="<?php echo $quantity; ?>"></td>
</tr>
<?php } } ?>
</tbody>
</table>
<button type="submit" name="Submit" class="btn btn-success btn-sm">Submit</button>
<form>
</body>
</html>
Advertisement
Answer
It is only inserting one row because you are only posting 1 product_id and 1 quantity
your posted data in php now looks like :
$_POST = [ 'product_id' => 1, 'quantity' => 9 ]
If you want to send the entire list through a post you will need to use the array syntax in your input name .
Something like
<td><input type="text" name="product[<?= htmlentities($product_id)?>][product_id]" value="<?= htmlentities($product_id); ?>"></td> <td><input type="text" name="product[<?= htmlentities($product_id)?>][quantity]" value="<?= htmlentities($quantity); ?>"></td>
in your php script this will result in a POST with an array value
$_POST = [
product => [
1 => [
'product_id' => 1,
'quantity' => 9
] ,
2 => [
'product_id' => 2,
'quantity' => 19
] ,
.....
]
]
which you can iterate with an foreach()
if( isset( $_POST['product'])) {
foreach( $_POST['product'] as $product) {
$productID=$product['product_id'];
$IssueQty=$product['quantity'];
// your sql insert here!. please use sql escaping on your user input!
}
}
However. With your script you keep inserting ALL rows you already have. I dont know if this is just as try/example but i assume you just want to insert new rows. How its setup now you keep inserting all rows you already have ( even without deleting the old ones)