-2

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>
Dharman
  • 30,962
  • 25
  • 85
  • 135
bornagain Agada
  • 3
  • 1
  • 3
    **Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Sep 28 '20 at 11:22

1 Answers1

0

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)

Erik
  • 86
  • 3