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 I want this: inventory_log TABLE Find my code below: Answer It is only inserting one row because you are only posting 1 product_id and 1 quantity your posted data in php now

Fetch array data from one table and insert into another table

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

JavaScript
​x
 
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

JavaScript
 
    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>

JavaScript
 
<?phpdefine('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 connectionif (mysqli_connect_errno()){ echo "Failed to connect to MySQL: " . mysqli_connect_error();}​// Insert into inventory_log TABLEif(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>​

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
]

JavaScript
 
$_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>

JavaScript
 
<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
       ]  ,  
       .....
    ]
]

JavaScript
 
$_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!
    }
}

JavaScript
 
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)

Advertisement

Answer