Course Content
Introduction to CodeIgniter
CodeIgniter is a powerful PHP framework built for developers who need a simple and elegant toolkit to create full-featured web applications.
0/3
MVC (Model-View-Controller)
MVC stands for Model-View-Controller. MVC is an application design model consisting of three interconnected parts. They include the model (data), the view (user interface), and the controller (processes that handle input).
0/6
Sessions
The Session class allows you to maintain a user’s "state" and track their activity while they browse your site.
0/1
URI Routing
There is a one-to-one relationship between a URL string and its corresponding controller class/method. The segments in a URI normally follow this pattern:
0/1
Forms and Input
Forms provide a way for users to interact with the application and submit data.
0/1
Composer
Composer is dependency manager in PHP. it allows you to declare the libraries your project depends on and it will manage (install/update) them for you.
0/1
Security
You can enable CSRF protection by modifying your application/config/config.php file
0/1
Working with Database
Like any other framework, we need to interact with the database very often and CodeIgniter makes this job easy for us. It provides a rich set of functionalities to interact with the database.
0/5
DataTable
DataTables is a table enhancing plug-in for the jQuery Javascript library that helps in adding sorting, paging, and filtering abilities to plain HTML tables with minimal effort. The main goal is to enhance the accessibility of data in normal HTML tables.
0/1
Spreadsheet
PhpSpreadsheet is a PHP library for reading and writing spreadsheet files. Importing Excel and CSV into MySQL help to save the user time and avoid repetitive work.
0/1
Payment Gateway
Razorpay and PayTM Payment Gateway
0/2
Chatbot
WhatsApp Chatbot and Telegram Chatbot
0/2
CodeIgniter 3
About Lesson

PhpSpreadsheet is a PHP library for reading and writing spreadsheet files. Importing Excel and CSV into MySQL help to save the user time and avoid repetitive work.

 

Software requirements

PHP version 7.2 or newer to develop using PhpSpreadsheet. Other requirements, such as PHP extensions, are enforced by the composer.

 

Installation

  1. Download and install CodeIgniter.
  2. Use Composer to install PhpSpreadsheet into your project:
    composer require phpoffice/phpspreadsheet
  3. Open application/config/config.php file and set your vendor directory path.
    $config['composer_autoload'] = 'vendor/autoload.php';
  4. Use phpspreadsheet the library inside in your controller

 

Create MySQL Database and Table

The following SQL query creates a user_info the table in the MySQL database.

CREATE TABLE `client_info` (
  `id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

Create Routes

Open application/config/routes.php file and add the following lines.

<?php 
$route['user/import']      = 'Upload/import';
?>

 

Create Model

Create a model file named User_model.php inside the application/models folder.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class User_model extends CI_Model {

	public function __construct() {
		parent::__construct();
		$this->table = 'user_info';
	}

	public function add($data) {
		$this->db->insert($this->table, $data);
		return $this->db->insert_id();
	}

	public function update($where, $data) {
		return $this->db->update($this->table, $data, $where);
	}

	public function delete($where) {
		return $this->db->delete($this->table, $where);
	}

	public function get($where = 0) {
		if($where) 
			$this->db->where($where);
		$query = $this->db->get($this->table);
		return $query->row();
	}

	public function get_all($where = 0, $order_by_column = 0, $order_by = 0) {
		if($where) 
			$this->db->where($where);
		if($order_by_column and $order_by) 
			$this->db->order_by($order_by_column, $order_by);
		$query = $this->db->get($this->table);
		return $query->result();
	}

	public function get_num_rows($where = 0) {
		if($where) 
			$this->db->where($where);
		$query = $this->db->get($this->table);		
		return $query->num_rows();
	}

	public function add_batch($data) {
		return $this->db->insert_batch($this->table, $data);
	}
}

 

Create Controller and load class

Create a controller named Upload.php and use the PHPSpreadsheet library inside the controller. See the following code for the controller.

<?php 
defined('BASEPATH') OR exit('No direct script access allowed');
date_default_timezone_set('Asia/Kolkata');
require 'vendor/autoload.php';

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

class Upload extends CI_Controller {
    
    public function __construct() {
        parent::__construct();
        // Load Model
        $this->load->model('User_model', 'user');
        $this->ip_address    = $_SERVER['REMOTE_ADDR'];
        $this->datetime      = date("Y-m-d H:i:s");
    }
    
    public function index() {
        $this->load->view("index");
    }
    
    public function display() {
        $data   = [];
        $data ["result"] = $this->user->get_all();
        $this->load->view("index", $data);
    }

    public function import() {
        $path       = 'documents/users/';
        $json       = [];
        $this->upload_config($path);
        if (!$this->upload->do_upload('file')) {
            $json = [
                'error_message' => 
                showErrorMessage($this->upload->display_errors()),
            ];
        } else {
            $file_data  = $this->upload->data();
            $file_name  = $path.$file_data['file_name'];
            $arr_file   = explode('.', $file_name);
            $extension  = end($arr_file);
            if('csv' == $extension) {
                $reader     = new PhpOfficePhpSpreadsheetReaderCsv();
            } else {
                $reader     = new PhpOfficePhpSpreadsheetReaderXlsx();
            }
            $spreadsheet    = $reader->load($file_name);
            $sheet_data     = $spreadsheet->getActiveSheet()->toArray();
            $list           = [];
            foreach($sheet_data as $key => $val) {
                if($key != 0) {
                    $result     = $this->user->get(["country_code" => $val[2], 
                    "mobile" => $val[3]]);
                    if($result) {
                    } else {
                        $list [] = [
                            'name'                  => $val[0],
                            'country_code'          => $val[1],
                            'mobile'                => $val[2],
                            'email'                 => $val[3],
                            'city'                  => $val[4],
                            'ip_address'            => $this->ip_address,
                            'created_at'            => $this->datetime,
                            'status'                => "1",
                        ];
                    }
                }
            }
            if(file_exists($file_name))
                unlink($file_name);
            if(count($list) > 0) {
                $result     = $this->user->add_batch($list);
                if($result) {
                    $json = [
                        'success_message'   => 
                        showSuccessMessage("All Entries are imported successfully."),
                    ];
                } else {
                    $json = [
                        'error_message'     => 
                        showErrorMessage("Something went wrong. Please try again.")
                    ];
                }
            } else {
                $json = [
                    'error_message' => 
                    showErrorMessage("No new record is found."),
                ];
            }
        }
        echo json_encode($json);
    }

    public function upload_config($path) {
        if (!is_dir($path)) 
            mkdir($path, 0777, TRUE);       
        $config['upload_path']      = './'.$path;       
        $config['allowed_types']    = 'csv|CSV|xlsx|XLSX|xls|XLS';
        $config['max_filename']     = '255';
        $config['encrypt_name']     = TRUE;
        $config['max_size']         = 4096; 
        $this->load->library('upload', $config);
    }
}

 

Create View – index.php

Create a view named index.php inside the application/views directory. See the following code for the view file.

<section class="content">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Upload</h3>
            <div class="card-tools">
            </div>
        </div>
        <div class="card-body">
            <form id="form-upload-user" method="post" autocomplete="off">
                <div class="sub-result"></div>
                <div class="form-group">
                    <label class="control-label">Choose File 
                    <small class="text-danger">*</small></label>
                    <input type="file" class="form-control form-control-sm" 
                    id="file" name="file" accept=".csv, 
                    application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, 
                    application/vnd.ms-excel" required>
                    <small class="text-danger">
                    Upload excel or csv file only.</small>
                    </div>
                <div class="form-group">
                    <div class="text-center">
                        <div class="user-loader" 
                        style="display: none; ">
                            <i class="fa fa-spinner fa-spin"></i> 
                            <small>Please wait ...</small>
                        </div>
                    </div>
                </div>
                <div class="form-group">
                    <button type="submit" 
                    class="btn btn-primary btn-sm waves-effect waves-light" 
                    id="btnUpload">Upload</button>
                </div>
            </form>
        </div>
    </div>
</section>

<script>
    $(document).ready(function() {
        $("body").on("submit", "#form-upload-user", function(e) {
            e.preventDefault();
            var data = new FormData(this);
            $.ajax({
                type: 'POST',
                url: "<?php echo base_url('user/import') ?>",
                data: data,
                dataType: 'json',
                contentType: false,
                cache: false,
                processData:false,
                beforeSend: function() {
                    $("#btnUpload").prop('disabled', true);
                    $(".user-loader").show();
                }, 
                success: function(result) {
                    $("#btnUpload").prop('disabled', false);
                    if($.isEmptyObject(result.error_message)) {
                        $(".result").html(result.success_message);
                    } else {
                        $(".sub-result").html(result.error_message);
                    }
                    $(".user-loader").hide();
                }
            });
        });
    });
</script>

Create View – display.php

Create a view named display.php inside the application/views directory. See the following code for the view file.

<section class="content">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title">Dipslay</h3>
            <div class="card-tools">
            </div>
        </div>
        <div class="card-body">
            <table class="table table-sm table-bordered 
            table-striped table-hover" width="100%">
                <thead>
                    <tr>
                        <th>No.</th>
                        <th>Name</th>
                        <th>Mobile</th>
                        <th>Email</th>
                        <th>City</th>
                    </tr>
                </thead>
                <tbody>
                <?php 
                $i = 0;
                foreach($result as $row) { 
                ?>    
                    <tr>
                        <td><?php ++$i; ?></td>
                        <td><?php echo $row->name; ?></td>
                        <td><?php echo $row->country_code.
                        $row->mobile; ?></td>
                        <td><?php echo $row->email; ?></td>
                        <td><?php echo $row->city; ?></td>
                    </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</section>