How to insert multiple rows in mysql database with codeigniter 4 framework?

Question

I am trying to make a web application using CodeIgniter 4 framework where I need to insert multiple rows in a single submit. I tried with codeIgniter 4 query builder insertBatch and got this error while inserting multiple rows in the MySQL database.

Error: ErrorException Undefined index: customerId

View:

    <?php $this->extend('dashboard'); ?>
<?php $this->section('content'); ?>


<script type="text/javascript">
    $(document).ready(function(){



        var html ='<tr><td><input type="text" name="customerId[]" value="<?=$customerId?>"></td><td><input type="text" name="transactionType[]" value="sell"></td><td><select name="productName[]"><?php 
                            foreach ($productsInfo as $product) {
                                echo '<option value="'.$product['productName'].'">'.$product['productName'].'</option>';
                            }
                        ?>
                    </select></td><td><input type="text" name="quantity[]"></td><td><input type="text" name="unit[]"></td><td><input type="text" name="price[]"></td><td><input type="text" name="payment[]"></td><td><input type="date" name="sellDate[]"></td><td><input type="button" class="btn btn-primary" id="remove" value="remove" name="remove"></td></tr>';

        $("#add").click(function(){
            $("#sellForm").append(html);
        });


        $("#sellForm").on('click','#remove',function(){
            $(this).closest('tr').remove();
        });

    });
</script>


<form class="p-2" method="post" action="<?=site_url('admin/sellDataSave')?>">
    <div class="table-responsive">
        <table class="table" id="sellForm">
            <tr class="bg-info text-center">
                <th>ID</th>
                <th>Type</th>
                <th>Product</th>
                <th>Quantity</th>
                <th>Unit</th>
                <th>Price</th>
                <th>Payment</th>
                <th>Date</th>
                <th>Action</th>
            </tr>
            <tr>
                <td><input type="text"  name="customerId[]" value="<?=$customerId?>"></td>
                <td><input type="text" name="transactionType[]" value="sell"></td>
                <td>
                    <select name="productName[]">
                        <?php 
                            foreach ($productsInfo as $product) {
                                echo '<option value="'.$product['productName'].'">'.$product['productName'].'</option>';
                            }
                        ?>
                    </select>
                </td>
                <td><input type="text" name="quantity[]"></td>
                <td><input type="text" name="unit[]"></td>
                <td><input type="text" name="price[]"></td>
                <td><input type="text" name="payment[]"></td>
                <td><input type="date" name="sellDate[]"></td>
                <td><input type="button" class="btn btn-primary" id="add" value="add" name="add"></td>
            </tr>
        </table>
        <center>
            <input type="submit" value="Submit" class="btn btn-primary">
        </center>
    </div>
</form>

<?php $this->endSection(); ?>

My function under Controller:

public function sellDataSave()
{
    $session = session();
    if (!$session->has('username')) 
    {
        return $this->response->redirect(site_url('home'));
    }
    else
    {   

            $data=[
            'customerId'=>$_POST['customerId'],
            'transactionType'=>$_POST['transactionType'],
            'productName'=>$_POST['productName'],
            'quantity'=>$_POST['quantity'],
            'unit'=>$_POST['unit'],
            'price'=>$_POST['price'],
            'payment'=>$_POST['payment'],
            'sellDate'=>$_POST['sellDate']
            ];


            $sellModel=new Sell();
            $sellModel->insertBatch($data);

    }
}

Model:

use CodeIgniter\Model;

class Sell extends Model
{
        protected $table = 'sell';
        protected $primaryKey = 'sellId';
        protected $allowedFields = ['customerId', 'sellId','transactionType','productName','quantity','price','payment','sellDate'];
}

I have submitted two rows and got this values after vardump($data).

array(8) { ["customerId"]=> array(2) { [0]=> string(2) "13" [1]=> string(2) "13" } ["transactionType"]=> array(2) { [0]=> string(4) "sell" [1]=> string(4) "sell" } ["productName"]=> array(2) { [0]=> string(19) "Poultry Feed Edited" [1]=> string(19) "Poultry Feed Edited" } ["quantity"]=> array(2) { [0]=> string(2) "67" [1]=> string(2) "78" } ["unit"]=> array(2) { [0]=> string(2) "kg" [1]=> string(2) "kg" } ["price"]=> array(2) { [0]=> string(4) "8996" [1]=> string(4) "8678" } ["payment"]=> array(2) { [0]=> string(4) "7896" [1]=> string(4) "7654" } ["sellDate"]=> array(2) { [0]=> string(10) "2020-05-14" [1]=> string(10) "2020-05-14" } }

The sell table structure given below:

Answer 1

Your $data variable is not correctly set for an insert batch. Each row of the array has to correspond to one new object you're wanting to insert.

This should work if your keys are the ones by default and that every row of $tmp_data has the same length.

$data = [];
$tmp_data = [
    'customerId' => $_POST['customerId'],
    'transactionType' => $_POST['transactionType'],
    'productName' => $_POST['productName'],
    'quantity' => $_POST['quantity'],
    'unit' => $_POST['unit'],
    'price' => $_POST['price'],
    'payment' => $_POST['payment'],
    'sellDate' => $_POST['sellDate']
];
foreach ($tmp_data as $k => $v) {
    for($i = 0; $i < count($v);$i++) {
        $data[$i][$k] = $v[$i];
    }
}

And be sure to take a look at the documentation : https://codeigniter.com/user_guide/database/query_builder.html?highlight=insertbatch