Import excel to multiple tables - PHP and MYSQL (codeigniter)

Question

Hello, with the function below, I can import the data from an excel spreadsheet to the database, via PHP.

To tables client contains the following fields:

   `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `address` VARCHAR(255) NOT NULL,

But, I need the columns: $invoice, $expiry, $value imported to another table invoice, which will contain the following fields:

    `client_id` INT(11) NOT NULL AUTO_INCREMENT,
    `invoice` VARCHAR(20) NOT NULL,
    `expiry` VARCHAR(20) NOT NULL,
    `value` VARCHAR(20) NULL DEFAULT NULL,

where, client_id will receive the client ID.

I am using the following method to import:

controller

function import()
    {
        if(isset($_FILES["file"]["name"]))
        {
            $path = $_FILES["file"]["tmp_name"];
            $object = PHPExcel_IOFactory::load($path);


            foreach($object->getWorksheetIterator() as $worksheet)
            {               
                $highestRow    = $worksheet->getHighestRow();
                $highestColumn = $worksheet->getHighestColumn();

                for($row = 2; $row getCellByColumnAndRow(0, $row)->getValue();
                    $address = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                    $invoice = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                    $expiry  = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                    $value   = $worksheet->getCellByColumnAndRow(4, $row)->getValue();

                    $data[] = [
                        'name'    =>    $name,
                        'address' =>    $address,
                        'invoice' =>    $invoice,
                        'expiry'  =>    $expiry,
                        'value'   =>    $value
                    ];
                }
            }
            $this->excel_import_model->insert($data);
            echo 'Data Imported successfully';
        }
    }

Model

function insert($data)
{
    $this->db->insert_batch('client', $data);
    $insert = $this->db->insert_id();
}

Answer 1

Look at it this way:

function only_number($str) {
    return preg_replace('/[^0-9]/', '', $str);
}

function import()
{
    if(isset($_FILES['file']['name']))
    {
        $path   = $_FILES['file']['tmp_name'];
        $object = PHPExcel_IOFactory::load($path);


        foreach($object->getWorksheetIterator() as $worksheet)
        {
            $highestRow    = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();

            for($row = 2; $row <= $highestRow; $row++)
            {
                $cpf_cnpj = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
                $name     = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                $contract = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                $invoice  = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                $expiry   = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
                $value    = $worksheet->getCellByColumnAndRow(5, $row)->getValue();
                $address  = $worksheet->getCellByColumnAndRow(6, $row)->getValue();
                $phone    = $worksheet->getCellByColumnAndRow(7, $row)->getValue();
                $email    = $worksheet->getCellByColumnAndRow(8, $row)->getValue();

                $cpf_cnpj = $this->only_number($cpf_cnpj);
                $cpf_cnpj = str_pad($cpf_cnpj, 11 , '0' , STR_PAD_LEFT);

                $phone = $this->only_number($phone);

                $person = [
                    'cpf_cnpj' =>   $cpf_cnpj,
                ];                                  

                $result_person = $this->db->get_where('tb_person', ['cpf_cnpj' => $cpf_cnpj])->result();

                if(count($result_person) == 0)
                {
                    // insert person
                    $this->db->insert('tb_person', $person);
                    $person_id = $this->db->insert_id();

                    // insert client
                    $_client = [
                        'person_id' => $person_id,
                    ];
                    $this->db->insert('tb_client', $_client);

                    // insert natural person
                    $_natural = [
                        'person_id' => $person_id,
                        'name'      => $name,
                    ];
                    $this->db->insert('tb_natural_person', $_natural);

                    // insert address
                    $_address = [
                        'address'   => $address,
                        'person_id' => $person_id,
                    ];
                    $this->db->insert('tb_address', $_address);
                }

                $person_id = $this->db->select('*')->from('tb_person')->where('cpf_cnpj', $cpf_cnpj)->get()->row()->id;

                // phone
                $result_phone = $this->db->get_where('tb_phone', ['phone' => $phone])->result();
                if(count($result_phone) == 0)
                {
                    $_phone = [
                        'person_id' => $person_id,
                        'phone'  => $phone,
                    ];
                    $this->db->insert('tb_phone', $_phone);
                }

                // email
                $result_email = $this->db->get_where('tb_email', ['email' => $email])->result();
                if(count($result_email) == 0)
                {
                    $_email = [
                        'person_id' => $person_id,
                        'email'  => $email,
                    ];
                    $this->db->insert('tb_email', $_email);
                }                   

                $result_contract = $this->db->get_where('tb_contract', ['contract' => $contract])->result();                    
                if(count($result_contract) == 0)
                {
                    // insert contract
                    $_contract = [
                        'person_id' => $person_id,
                        'contract'  => $contract,
                    ];
                    $this->db->insert('tb_contract', $_contract);
                    $contract_id = $this->db->insert_id();
                }

                $contract_id = $this->db->select('*')->from('tb_contract')->where('contract', $contract)->get()->row()->id;

                $result_invoice = $this->db->get_where('tb_invoice', ['invoice' => $invoice, 'contract_id' => $contract_id])->result();
                // invoice
                $_invoice = [
                    'contract_id' => $contract_id,
                    'person_id' => $person_id,
                    'invoice'   => $invoice,
                    'expiry'    => $expiry,
                    'value'     => $value
                ];

                if(count($result_invoice) == 0)
                {
                    $this->db->insert('tb_invoice', $_invoice);
                }
                else
                {
                    $this->db->where('invoice', $invoice);
                    $this->db->where('contract_id', $contract_id);
                    $this->db->update('tb_invoice', $_invoice);
                }
            }
        }
    }
}

Tables

USE `ci_import`;

DROP TABLE IF EXISTS `tb_person`;
CREATE TABLE IF NOT EXISTS `tb_person` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `cpf_cnpj` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_client`;
CREATE TABLE IF NOT EXISTS `tb_client` (
    `person_id` INT(11) NOT NULL,
    PRIMARY KEY (`person_id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_natural_person`;
CREATE TABLE IF NOT EXISTS `tb_natural_person` (
    `person_id` INT(11) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`person_id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_address`;
CREATE TABLE IF NOT EXISTS `tb_address` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `person_id` INT(11) NOT NULL,
    `address` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_phone`;
CREATE TABLE IF NOT EXISTS `tb_phone` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `person_id` INT(11) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_email`;
CREATE TABLE IF NOT EXISTS `tb_email` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `person_id` INT(11) NOT NULL,
    `email` VARCHAR(160) NOT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_contract`;
CREATE TABLE IF NOT EXISTS `tb_contract` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `contract` VARCHAR(255) NOT NULL,
    `person_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tb_invoice`;
CREATE TABLE IF NOT EXISTS `tb_invoice` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `contract_id` INT(11) NOT NULL,
    `person_id` INT(11) NOT NULL,
    `invoice` VARCHAR(20) NOT NULL,
    `expiry` VARCHAR(20) NOT NULL,
    `value` VARCHAR(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;