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:

Can you var_dump the post values you are returning as post and also can you show us the migration file for your table ? — Dhaval Chheda, May 06 '20 at 06:03
@DhavalChheda I added the result of var_dump values and table structure in my post. Please have a look at it. — Monayem Islam, May 06 '20 at 08:19

ViLar · Accepted Answer · 2020-05-06T12:38:06.883

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

TV-C-1-5 · Answer 2 · 2023-04-15T19:26:34.543

0

Gather your 'multiple row' data as separate arrays, - i.e. "$array_1, $array_2" etc.; into a '$data" array. Then pass the '$data" array - that contains all rows - as a parameter, to the CodeIgniter db function 'insertBatch()'. The insertBatch() will insert each row. See: https://codeigniter.com/user_guide/database/query_builder.html?highlight=insert#insertbatch

$data[$array_1, $array_2, $array_3];

$db = db_connect();
      if($db->table('sell')->insertBatch($data)){
          echo "success - batch inserted.";
      }
// Produces: INSERT INTO mytable (column) VALUES ('value'),  (column) VALUES ('value')

edited Apr 15 '23 at 19:26

answered Apr 15 '23 at 01:52

TV-C-1-5

680
2
13
19

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Apr 15 '23 at 12:19

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

2 Answers2