Insertion of multiple records in mysql using php

Question

I have been trying to insert more than one records into my database table, but all i get is zeros(0) inserted into the fields. Below is what I tried. I tried using the foreach loop for insertion, but its not working for me. There any easy way to do it.Thanks

The html

<form method="post" action="data_handlers/purchase_entry_process.php">
    <table>
    <thead>
    <tr class="titlerow">
    <th>Item name</th>
    <th>Quantity</th>
    <th>Purchase Price</th>
    <th>Amount</th>
    <th>Description</th>
    </tr>
    </thead>
        <tr>
        <td class="col-md-3"><select name="item_code[]">
        <option></option>
        <?php
        $tempholder = array();
        $query = $link->query("SELECT * FROM items");
        $nr = mysqli_num_rows($query);
        for($i=0; $i<$nr; $i++) {
        $row = mysqli_fetch_array($query);
        if(!in_array($row['item_code'],$tempholder)) 
        {
        echo"<option value=\"{$row['item_code']}\" >{$row['item_name']}</option>";
        }
        }  
        ?>
        </select></td>
        <td><input type="text" name="qty"></td>
        td><input type="text" name="purchase_price"></td>
        <td><input type="text" name="sub_total"></td>
        <td><input type="text" name="description"></td>                     
        </tr>
        <tr>
        <td class="col-md-3"><select name="item_code[]">
        <option></option>
        <?php
        $tempholder = array();
        $query = $link->query("SELECT * FROM items");
        $nr = mysqli_num_rows($query);
        for($i=0; $i<$nr; $i++) {
        $row = mysqli_fetch_array($query);
        if(!in_array($row['item_code'],$tempholder)) 
        {
        echo"<option value=\"{$row['item_code']}\" >{$row['item_name']}</option>";
        }
        }  
        ?>
        </select></td>
        <td><input type="text" name="qty"></td>
        td><input type="text" name="purchase_price"></td>
        <td><input type="text" name="sub_total"></td>
        <td><input type="text" name="description"></td>                     
        </tr>
    </table>
    </form>

php

$item_code = $_POST['item_code'];
$qty = $_POST['qty'];
$pur_price = $_POST['purchase_price'];
$sub_total = $_POST['sub_total'];
$desc = $_POST['description'];

foreach($item_code as $item_codes);
$insert_inventory = $link->query("INSERT INTO inventory VALUES ('NULL','$item_codes','$qty','$pur_price','$qty','$desc','$sub_total')");

You're attempting to insert an array as $item_code. Quantity, price, total and description only contain information from the second row of input fields. Here's a dump of a POST request from your form, first row received ones (1) as input, second row received twos (2). array(5) { ["item_code"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["qty"]=> string(1) "2" ["purchase_price"]=> string(2) "22" ["sub_total"]=> string(3) "222" ["description"]=> string(4) "2222" } — Baldvin Th
– Baldvin Th, Commented Feb 14, 2015 at 14:28

Baldvin Th · Accepted Answer · 2015-02-14 14:53:09Z

1

To get the quantity, price, sub_total and description from all the fields, you should make them arrays like you did with item_code.

<td><input type="text" name="qty[]"></td>
<td><input type="text" name="purchase_price[]"></td>
<td><input type="text" name="sub_total[]"></td>
<td><input type="text" name="description[]"></td>

For processing the PHP and make it ready for the query you could do something akin to

<?php
    // Prepare the query
    $query = "INSERT INTO inventory VALUES ";

    // Go through all the entries
    foreach ($_POST['item_code'] as $key => $value) {

        // If an item has been selected from the drop-down
        // we process it for the query.
        if (!empty($value) && $value != "") {
            $query .= ($key > 0 ? ", " : "");
            $query .= "(NULL, '" . $value . "', '" . $_POST['qty'][$key] . "', '" . $_POST['purchase_price'][$key] . "', '" . $_POST['sub_total'][$key] . "', '" . $_POST['description'][$key] . "')";
        }
    }

    // Example output from $query
    // INSERT INTO inventory VALUES (NULL, '1', '1', '11', '111', '1111'), (NULL, '2', '2', '22', '222', '2222')
?>

I would recommend throwing in some escaping of the variables as well, to prevent SQL injections.

answered Feb 14, 2015 at 14:53

Baldvin Th

2802 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ASM Over a year ago

I did that but it only insert one record still. Thanks

ASM Over a year ago

I think its the query that is confusing me. can you please break it down for me. I have other input to add to the query. Thanks

Baldvin Th Over a year ago

The $query variable should contain the finished string you would then execute with your $link->query($query) function. If you're in doubt of what $query is returning, try echoing it to receive the value.

ASM Over a year ago

The problem is from the item_code selection. when i echo the $query, i get the following INSERT INTO inventory VALUES ('NULL','2015-02-15','','1002116','Array', 'Array','0',0,'Array','suadik','purchase','Array','Array','14295777853664845') the item_code does not appear, but the rest appear as Array. the item_code is suppose to appear after the date.

Baldvin Th Over a year ago

Obviously you have made changes to the code supplied, as you have 14 fields in your most recent reply, while your example provided no more than 6. Regardless the design principle is the same, where you receive 'Array' you haven't provided the [$key] argument, or you have a nested array. When in doubt do this echo "<pre>"; var_dump($_POST); echo "</pre>"; to look at what you are receiving from your form.

Collectives™ on Stack Overflow

Insertion of multiple records in mysql using php

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related