How to perform insert batch in CodeIgniter 4

Sometimes we need to perform some inserting batch of our data to database when we have a lot of data and its not from a single form. Just like in CodeIgniter 3, in CodeIgniter 4 we have function that it could be used easily and without doing looping manualy.

When insert batch should be used?

As you know, sometimes we have big data that need to be import from xls or csv and it can be hard and slow when we looping it manualy. Or we have array of data already that we need to insert into our database, even we could be do it by using for or foreach looping but CodeIgniter 4 have insert batch function that can handle this.

How to perform insert batch in CodeIgniter 4?

Basicaly we can handle this in our modle, the function will generate strings from data that we supply, and run the query automatically. This feature can handle our array or object data, that we parse. As example, maybe you can parse the array of data and send it to modle that perform insert batch function.

Here is the example.

$data = [
    [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date',
    ],
    [
        'title' => 'Another title',
        'name'  => 'Another Name',
        'date'  => 'Another date',
    ],
];

$builder->insertBatch($data);

In above example, the $data parameter is an array. And the all we need just perform $builder->insertBatch($data). Just it. The $builder is variable that already configured before to connect database. Why there is no table defined? Yep, the table have been defined before at te top, just something like this.

$db      = \Config\Database::connect();
$builder = $db->table('users');

Complete Example

Well, let say that we have controller called User controller, that will handle our user data and parse this data to UserModel that will perform insert batch function.

Then, we will have the controller like this:

<?php namespace App\Controllers;

use App\Models\UserModel;

class User extends BaseController
{
	protected $UserModel;

	public function __construct() {

        $this->UserModel = new UserModel();

    }

	public function insert_batch()
	{
		$data = [
		    [
		        'title' => 'My title',
		        'name'  => 'My Name',
		        'date'  => 'My date',
		    ],
		    [
		        'title' => 'Another title',
		        'name'  => 'Another Name',
		        'date'  => 'Another date',
		    ],
		];

		$insert = $this->UserModel->insert_batch($data);

		return $result = ($insert) ? true : false ;

	}

}

Then we will have the UserModel.php like this one:

<?php namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model
{
    protected $table = 'user';

    public function __construct() {

        parent::__construct();
        $db = \Config\Database::connect();
        $this->builder = $db->table($this->table);
    }

    function input_data($data){
      $process = $this->builder->insertBatch($data); 
      if ($process) {
        return true;
      }else{
        return false;
      }
    }
}

in the model above, we predefined the $table as protected parameters, it will help us to create more function and maintain it easily in next step. Then, we also have database connection as $this->builder that also connect the table that we defined in _construct. So, it could be called in the next function that you will create.

From the example above, it will produce and run the query like this

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date')

Welldone, just it. Hope it will help you!



0 0 votes
Article Rating
Subscribe
Notify of
guest

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ggive
Ggive
9 months ago

Hi,

Thanks for sharing.

Small note :
In your insert_batch() Controller function, you should call $this->UserModel->input_data($data) of your model instead of $this->UserModel->insert_batch($data);
insert_batch doesn’t exist in your model.

regards,


2
0
Would love your thoughts, please comment.x
()
x