vahid najafi vahid najafi - 5 months ago 130
PHP Question

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.

Answer

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%'