Fil Fil - 5 months ago 19
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

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')
Comments