CODEWITHSUNDEEP
phpmysqlcodeignitersql-insertcodeigniter-3
Vahid Najafi
Vahid Najafi

Reputation: 5263

Codeigniter insert_batch limitation issue

I'm using codeigniter insert_batch() function instead of looping the data with simple insert. Number of my rows is about 390 and just 100 of them insert as codeigniter (or mysql) doesn't allow to insert more than 100 rows in a single query.

Then I separated 100 by 100 with array_chunk function, like this:

$all_hafars = array_chunk($hafar_co,100);
foreach ($all_hafars as $hafar) {
    $this->db->insert_batch('hafar_co', $hafar);
}

Again only 100 of them insert! Any idea?

Edit : I even use simple insert function with transaction. When I use transaction, again it insert only 100 rows.

Upvotes: 4

Views: 5262

Answers (2)

Parth Patel
Parth Patel

Reputation: 521

PHP's queries are limited by the max_allowed_packet configuration option. It defines the absolute length limit, in characters, that a query string can be. Note that this isn't just the total size of the data being inserted, it's the entire query string. SQL commands, punctuation, spaces, etc...

SHOW VARIABLES WHERE Variable_name LIKE  '%max_allowed_packet%'

Upvotes: 4

Kristiyan
Kristiyan

Reputation: 1663

insert_batch Is going to prevent some mysql limit problems. I think that it's related to configuration in your MySQL. The main idea is to avoid if you try to cross something.

I suggest you to use transaction instead of insert_batch.

You can read more about transactions here: https://www.codeigniter.com/user_guide/database/transactions.html

You can read more about your problem here: https://github.com/bcit-ci/CodeIgniter/issues/2680

Upvotes: 1

Related Questions