How to insert data into a database which uses the same input name variable for multiple rows in a table?

Question

I am currently trying to design an invoice generator. Essentially the page has input fields along side a table which allows you to create extra rows to input more data.

I need this data to be stored in database to be called in the future to be turned into a downloadable .pdf

My current issue is getting the data from the table into the database. So far my code only inputs the first row of data. Am i going the complete wrong way about this?

<tr id="item0" class="item">
    <td>
        1
    </td>
    <td>
        <input name='item_name[0]' type='text' placeholder='Name' class='form-control input-md' />
    </td>
    <td>
        <input name='quantity[0]' type='text' placeholder='0' class='form-control input-md' />
    </td>
    <td>
        <input name='price[0]' type='text' placeholder='&pound;0.00' class='form-control input-md' />
    </td>
    <td>
        total
    </td>
</tr>
<tr id="item1" class="item">
    <td>
        1
    </td>
    <td>
        <input name='item_name[1]' type='text' placeholder='Name' class='form-control input-md' />
    </td>
    <td>
        <input name='quantity[1]' type='text' placeholder='0' class='form-control input-md' />
    </td>
    <td>
        <input name='price[1]' type='text' placeholder='&pound;0.00' class='form-control input-md' />
    </td>
    <td>
        total
    </td>
</tr>

And the php...

if (isset($_POST['submit'])) {
    print_r(array_values($_POST['item_name']));
}
$i = 0;
foreach($_POST as $val) {
    $item_name = $_POST['item_name'][$i];
    $quantity = $_POST['quantity'][$i];
    $price = $_POST['price'][$i];

    $sqlConnect - > query("INSERT INTO `Invoices` (`id`, `item_name`, `quantity`, `price`) VALUES ('".$i.
        "', '".$item_name.
        "', '".$quantity.
        "', '".$price.
        "')");
    $i++;
}
}
else {
    die('No item data to process');
}

Why do you iterate over `$_POST`? Number of keys in `$_POST` may not be equal to number of your item names. — u_mulder, Jul 16 '17 at 21:03
the loop is wrong but @u_mulder it should still work for two rows - $i will range from 0 to 5. — inarilo, Jul 16 '17 at 21:05
How would i got about finding the number of item names instead? I found this loop example after hours of research — Tÿler, Jul 16 '17 at 21:06
You can use `foreach($_POST['item_name'] as $i=>$item_name)` and do away with setting $i or $item_name separately. — inarilo, Jul 16 '17 at 21:10
Thanks, and what about the other 2 arrays, how do i include them in the loop — Tÿler, Jul 16 '17 at 21:12
Possible duplicate of [Insert data from multiple array form inputs using php and mysql](https://stackoverflow.com/questions/35985375/insert-data-from-multiple-array-form-inputs-using-php-and-mysql) — Shadow, Jul 16 '17 at 21:21

simon.ro · Answer 1 · 2017-07-16T21:39:20.687

0

Assuming the id field is an auto-incrementing primary key of your Invoices table: Leave that one, when inserting. Your current code always tries to insert 1 or 2 as id for every request.

If its not an auto-increment you need to generate a valid id in your app, $i alone won't do the trick.

foreach ($_POST['item_name'] as $index => $itemName) {
    $quantity = $_POST['quantity'][$index];
    $price = $_POST['price'][$index];

    // DONT DO THIS! Its prone to SQL injection 
    $sqlConnect->query("INSERT INTO `Invoices` (`item_name`, `quantity`, `price`) VALUES ('".$itemName.
        "', '".$quantity.
        "', '".$price.
        "')");
}

Not sure what your db-connection library is, but currently your code is prone to SQL injection attacks. You should use prepared statements and parameterization. Please see what Bobby Tables says.

edited Jul 16 '17 at 21:39

answered Jul 16 '17 at 21:30

simon.ro

2,984
2
22
36

This just gives me an error, as for the sql injection prevention it is something i have yet to look into – Tÿler Jul 16 '17 at 21:42
Who gives an error? php? database? What line? What message? – simon.ro Jul 16 '17 at 21:44
Thats probably just what you see in your browser. You should check the error_log file on your server – simon.ro Jul 16 '17 at 21:51

Hamid Asghari · Answer 2 · 2017-07-16T22:19:11.393

To have a real dynamic form, where undefined number of items can be entered, you need JavaScript. but for now, I would suggest to change your form to something like this:

<input name='items[1][name]'>
<input name='items[1][quantity]'>
<input name='items[1][price]'>
<!-- and for second item: -->
<input name='items[2][name]'>
<input name='items[2][quantity]'>
<input name='items[2][price]'>

so your $_POST data structure will be like this:

items:[
        [
          quantity:
          price:
          name: 
        ],
        [
          quantity:
          price:
          name: 
        ],
]

This way, you can loop through items, not item properties. and your loop will be like this:

if (isset($_POST['submit'])) {
    var_dump($_POST);
    foreach($_POST['items'] as $item){
         $sql = 'INSERT INTO invoices (name, quantity, price)
                 VALUES ($item['name'], $item['quantity'], $item['price'])';
         $sqlConnect->query($sql);
    }
} else {
    die('No item data to process');
}

Note that in MySQL you can set id field to auto_increment, so no need to include it in your query.

Also, use var_dump() to know what you are dealing with, like var_dump($_POST);

I would use javascript if i knew how, i at least have some understanding of php. I also get an internal server error using this solution and it isn't because you have missed a closing } in the for loop — Tÿler, Jul 16 '17 at 22:14
unfortunately i do not have access to error logs on my server — Tÿler, Jul 16 '17 at 22:27

How to insert data into a database which uses the same input name variable for multiple rows in a table?

2 Answers2