Fil Fil - 1 year ago 55
SQL Question

Best way to insert large record to the database table imported from csv

I have the following code written in codeigniter.

foreach ($data as $value) {
# item_transactions fields and values
$data = array (
'checklist_item_id' => ($this->set['table'] == 'checklist_item') ? $this->set['id'] : null,
'imp_sequence_no' => $value['Sequence No.'],
'imp_vendor_tin' => $value['Vendor TIN'],
'imp_vendor_name' => $value['Vendor Name'],
'imp_input_vat_per_client' => floatval(str_replace(',', '', trim($value['Input VAT per client']))),
'imp_gsi' => $value['Goods/Services/Importations']
# Insert to item_transactions table
$id = parent::insert($data);
return $count;

What this code do, is to insert a thousands of records to a database table, from the csv file imported.

is an associative array of fields and values to be inserted.

The problem

When I tried to import a csv file containing more than 4,000 records, I got a PHP error saying
Maximum execution time of 30 seconds exceeded.

I don't want to change the php.ini setting, because mostly you don't have control on the live servers settings.

Does anyone can suggest best way to solve this problem?


Codeigniter has a query builder class that supports batch upload. You can either pass an array or an object to the function. Here is an example using an array:

$data = array(
                'title' => 'My title',
                'name' => 'My Name',
                'date' => 'My date'
                'title' => 'Another title',
                'name' => 'Another Name',
                'date' => 'Another date'

$this->db->insert_batch('mytable', $data);


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