Fil Fil - 1 year ago 60
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);
$count++;
}
return $count;


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

$data
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(
        array(
                'title' => 'My title',
                'name' => 'My Name',
                'date' => 'My date'
        ),
        array(
                'title' => 'Another title',
                'name' => 'Another Name',
                'date' => 'Another date'
        )
);

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

Producess:

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