CODEWITHSUNDEEP
phpmysqlcodeignitercsv
Fil
Fil

Reputation: 8863

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?

Upvotes: 0

Views: 642

Answers (2)

claudios
claudios

Reputation: 6656

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

Upvotes: 1

Amit Ray
Amit Ray

Reputation: 3485

Change your php.ini file settings. max_execution_time = 300 will be enough. Restart your server.

Upvotes: 1

Related Questions