Insert limit in local WAMP or XAMPP server

Question

I'm trying to import an excel file with 15,000 record in a local php and mysql system, but it's always stop inserting in after 3000 records and ignoring the rest of records. Even in the hosted copy of the system it's insert only 3027 record.

Can I get some help please?

Import csv php script

if ($this->form_validation->run() == true) {
        if (isset($_FILES['userfile'])) {

            $this->load->library('upload');
            $config['upload_path']   = $this->digital_upload_path;
            $config['allowed_types'] = 'csv';
            $config['max_size']      = $this->allowed_file_size;
            $config['overwrite']     = true;
            $config['encrypt_name']  = true;
            $config['max_filename']  = 25;
            $this->upload->initialize($config);
            if (!$this->upload->do_upload()) {
                $error = $this->upload->display_errors();
                $this->session->set_flashdata('error', $error);
                admin_redirect('products/import_csv');
            }
            $csv = $this->upload->file_name;
            $arrResult = [];
            $handle    = fopen($this->digital_upload_path . $csv, 'r');
            if ($handle) {
                while (($row = fgetcsv($handle, 15000, ',')) !== false) {
                    $arrResult[] = $row;
                }
                fclose($handle);
            }
            $titles  = array_shift($arrResult);
            $updated = 0;
            $items   = [];
            foreach ($arrResult as $key => $value) {
                $supplier_name = isset($value[24]) ? trim($value[24]) : '';
                $supplier      = $supplier_name ? $this->products_model->getSupplierByName($supplier_name) : false;

                $item = [
                    'name'              => isset($value[0]) ? trim($value[0]) : '',
                    'code'              => isset($value[1]) ? trim($value[1]) : '',
                    'barcode_symbology' => isset($value[2]) ? mb_strtolower(trim($value[2]), 'UTF-8') : '',
                    'brand'             => isset($value[3]) ? trim($value[3]) : '',
                    'category_code'     => isset($value[4]) ? trim($value[4]) : '',
                    'unit'              => isset($value[5]) ? trim($value[5]) : '',
                    'sale_unit'         => isset($value[6]) ? trim($value[6]) : '',
                    'purchase_unit'     => isset($value[7]) ? trim($value[7]) : '',
                    'cost'              => isset($value[8]) ? trim($value[8]) : '',
                    'price'             => isset($value[9]) ? trim($value[9]) : '',
                    'alert_quantity'    => isset($value[10]) ? trim($value[10]) : '',
                    'tax_rate'          => isset($value[11]) ? trim($value[11]) : '',
                    'tax_method'        => isset($value[12]) ? (trim($value[12]) == 'exclusive' ? 1 : 0) : '',
                    'image'             => isset($value[13]) ? trim($value[13]) : '',
                    'subcategory_code'  => isset($value[14]) ? trim($value[14]) : '',
                    'variants'          => isset($value[15]) ? trim($value[15]) : '',
                    'cf1'               => isset($value[16]) ? trim($value[16]) : '',
                    'cf2'               => isset($value[17]) ? trim($value[17]) : '',
                    'cf3'               => isset($value[18]) ? trim($value[18]) : '',
                    'cf4'               => isset($value[19]) ? trim($value[19]) : '',
                    'cf5'               => isset($value[20]) ? trim($value[20]) : '',
                    'cf6'               => isset($value[21]) ? trim($value[21]) : '',
                    'hsn_code'          => isset($value[22]) ? trim($value[22]) : '',
                    'second_name'       => isset($value[23]) ? trim($value[23]) : '',
                    'supplier1'         => $supplier ? $supplier->id : null,
                    'supplier1_part_no' => isset($value[25]) ? trim($value[25]) : '',
                    'supplier1price'    => isset($value[26]) ? trim($value[26]) : '',
                    'slug'              => $this->sma->slug($value[0]),
                ];

                if ($catd = $this->products_model->getCategoryByCode($item['category_code'])) {
                    $tax_details   = $this->products_model->getTaxRateByName($item['tax_rate']);
                    $prsubcat      = $this->products_model->getCategoryByCode($item['subcategory_code']);
                    $brand         = $this->products_model->getBrandByName($item['brand']);
                    $unit          = $this->products_model->getUnitByCode($item['unit']);
                    $base_unit     = $unit ? $unit->id : null;
                    $sale_unit     = $base_unit;
                    $purcahse_unit = $base_unit;
                    if ($base_unit) {
                        $units = $this->site->getUnitsByBUID($base_unit);
                        foreach ($units as $u) {
                            if ($u->code == $item['sale_unit']) {
                                $sale_unit = $u->id;
                            }
                            if ($u->code == $item['purchase_unit']) {
                                $purcahse_unit = $u->id;
                            }
                        }
                    } else {
                        $this->session->set_flashdata('error', lang('check_unit') . ' (' . $item['unit'] . '). ' . lang('unit_code_x_exist') . ' ' . lang('line_no') . ' ' . ($key + 1));
                        admin_redirect('products/import_csv');
                    }

                    unset($item['category_code'], $item['subcategory_code']);
                    $item['unit']           = $base_unit;
                    $item['sale_unit']      = $sale_unit;
                    $item['category_id']    = $catd->id;
                    $item['purchase_unit']  = $purcahse_unit;
                    $item['brand']          = $brand ? $brand->id : null;
                    $item['tax_rate']       = $tax_details ? $tax_details->id : null;
                    $item['subcategory_id'] = $prsubcat ? $prsubcat->id : null;

                    if ($product = $this->products_model->getProductByCode($item['code'])) {
                        if ($product->type == 'standard') {
                            if ($item['variants']) {
                                $vs = explode('|', $item['variants']);
                                foreach ($vs as $v) {
                                    if (!empty(trim($v))) {
                                        $variants[] = ['product_id' => $product->id, 'name' => trim($v)];
                                    }
                                }
                            }
                            unset($item['variants']);
                            if ($this->products_model->updateProduct($product->id, $item, null, null, null, null, $variants)) {
                                $updated++;
                            }
                        }
                        $item = false;
                    }
                } else {
                    $this->session->set_flashdata('error', lang('check_category_code') . ' (' . $item['category_code'] . '). ' . lang('category_code_x_exist') . ' ' . lang('line_no') . ' ' . ($key + 1));
                    admin_redirect('products/import_csv');
                }

                if ($item) {
                    $items[] = $item;
                }
            }
        }

        // $this->sma->print_arrays($items);
    }

Insert script

public function add_products($products = [])
{
    if (!empty($products)) {
        foreach ($products as $product) {
            $variants = explode('|', $product['variants']);
            unset($product['variants']);
            if ($this->db->insert('products', $product)) {
                $product_id = $this->db->insert_id();
                foreach ($variants as $variant) {
                    if ($variant && trim($variant) != '') {
                        $vat = ['product_id' => $product_id, 'name' => trim($variant)];
                        $this->db->insert('product_variants', $vat);
                    }
                }
            }
        }
        return true;
    }
    return false;
}

Does this answer your question? [How to import more than 100000 records into a mysql database?](https://stackoverflow.com/questions/33580178/how-to-import-more-than-100000-records-into-a-mysql-database) — Ahmed Sunny, Jun 07 '20 at 18:16
I changed my php config file settings but it still imports only 3026 records only @AhmedSunny — Eng.Wagdi, Jun 07 '20 at 18:53
I think the problem is not timeout, it's about mysql config because the 15,000 record imported in the same system hosted online. — Eng.Wagdi, Jun 07 '20 at 18:57
https://stackoverflow.com/a/20264467/2996989 look at this then, try changing config — Ahmed Sunny, Jun 07 '20 at 21:32
Give us some detail like your config, how do you run it, how much the file size and another detail, so we can help you out of trouble. — Eko Junaidi Salam, Jun 08 '20 at 00:33
CSV file size 1784 KB, contains 15,000 records of products, codeigniter controller extracts the 15,000 records as array and passing it to the model, the model receives an array with 15,000 record size but it's inserts only 3026 records any advice my friend!. @EkoJunaidiSalam — Eng.Wagdi, Jun 08 '20 at 16:21
Ok, I get it. Please consider edit your question regarding your detail and please add some snippet code so we can see your current script that produce some abnormal behavior... — Eko Junaidi Salam, Jun 09 '20 at 03:59
`$this->db->insert('product_variants', $vat);` – you are not checking whether this part actually succeeded. — CBroe, Jun 09 '20 at 13:00
All products has no variants so this part never executed, I thing there is some settings that limits the amount of insert @CBroe — Eng.Wagdi, Jun 09 '20 at 15:23

Eko Junaidi Salam · Accepted Answer · 2020-06-10T18:41:27.050

I think you're using codeigniter, so i recommend you to use yield (generator in php) and insert_bulk to handle some big data or some huge records to optimize your code and keep your cpu/memory not to leaks or timeout.

Here's some logic :

Import your csv and store it in generator.
Process your titles, key values from csv in one process,DO NOT store it in array variable except your titles only.
Convert the generator to array, and do your another process.
Do your logic to insert the data and Store your inserted data to generator.
Convert the generator to array and insert it using insert_batch function from codeigniter.

Actually you can combine poin 4 to the poin 2 so you have one process do all the logic and the result is the data you want to insert to the database.

Here's my simple code how you implement the logic :

<?php
// I think you're using codeigniter as your framework here, so i recommend you using yield and insert_bulk to insert the data
$filename = "testdata.csv";
$titles = [];

$readcsv = function($filename)use(&$titles){
    $keys = ['name','code','barcode_symbology','brand','category_code','unit','sale_unit','purchase_unit','cost','price','alert_quantity','tax_rate','tax_method','image','subcategory_code','variants','cf1','cf2','cf3','cf4','cf5','cf6','hsn_code','second_name','supplier1','supplier1_part_no','supplier1price','slug'];
    $handle = fopen($filename,"r");
    $first = true;
    while (($data = fgetcsv($handle, 15000, ",")) !== FALSE) {
        if($first){
            $titles = $data;
            $first = false;
        }else{
            if(count($keys) == count($data)){
                yield array_combine($keys,$data);
            }
        }
    }
    fclose($handle);
};

// Your csv data will be in this variable and your $titles now have value
$yourdata = iterator_to_array($readcsv($filename));

// This function store your inserted data to the yield (generator in php) if the conditions true
$list_insert_data = function($yourdata){
    // Loop your csv data and do your logic here...
    $break = false;
    $cnt = 1;
    foreach($yourdata as $v){
        if(!empty($v) && !$break){
            yield $v;
        }

        // Just simple logic to store 3 records from the csv
        if($cnt == 3){
            $break = true;
        }else if($break){
            break;
        }
        $cnt++;
    }
};

// This variable contains 3 array from the csv data
$inserted = iterator_to_array($list_insert_data($yourdata));

// Then insert the data to the database.
$this->db->insert_batch('product_variants', $inserted);

UPDATED:

If you're using mysql, you need to check your max_allowed_packet and check it in your mysql SHOW VARIABLES WHERE Variable_name LIKE '%max_allowed_packet%' and increase or double the value.

Change in the my.ini file by including the single line under [mysqld] or [client] section in your file:

max_allowed_packet=1024M

then restart the MySQL service and you are done. Hope this simple logic help you out of trouble.

score 0 · Answer 2 · answered Jun 10 '20 at 18:44

0

You need to change the max_execution_time on php.ini

max_execution_time = 5000

Or on top of your script, add the following line.

ini_set('max_execution_time', 5000);

answered Jun 10 '20 at 18:44

Iynga Iyngaran Iyathurai

796
1
9
14

Insert limit in local WAMP or XAMPP server

2 Answers2