Fil Fil - 2 years ago 90
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?

Answer Source

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')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download