How to Import Data in MySQL with CodeIgniter 3

CSV files are easier to maintain and can be used for export and import data in the web application.

For data import first, need to allow the users to browse and upload the file. In CodeIgniter, there is upload library available that makes it easier to handle file uploads.

In this tutorial, I show how you can import data of CSV file in the MySQL database table in the CodeIgniter 3 project.

---

Contents

1. Table structure
2. Configuration
3. Model
4. Controller
5. View
6. Conclusion

---

1. Table structure

I am using users table in the demonstration.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

2. Configuration

Navigate to application/config/database.php and define the Database connection.

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root', // Username
 'password' => '', // Password
 'database' => 'tutorial', // Database name
 'dbdriver' => 'mysqli',
 'dbprefix' => '',
 'pconnect' => FALSE,
 'db_debug' => (ENVIRONMENT !== 'production'),
 'cache_on' => FALSE,
 'cachedir' => '',
 'char_set' => 'utf8',
 'dbcollat' => 'utf8_general_ci',
 'swap_pre' => '',
 'encrypt' => FALSE,
 'compress' => FALSE,
 'stricton' => FALSE,
 'failover' => array(),
 'save_queries' => TRUE
);

Default controller

Open application/config/routes.php and edit default_controller value to Users.

$route['default_controller'] = 'Users';

Load Database

To access the MySQL database require loading database library.

Open application/config/autoload.php and add the database in libraries array().

$autoload['libraries'] = array("database");

---

3. Model

Create a new Main_model.php file in application/models/ directory.

Define insertRecord() method to insert record in users table. It takes an Array as a parameter.

Check user already exit or not by username if not then define an Array $newuser and pass in $this->db->insert('users',$newuser).

Completed Code

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

class Main_model extends CI_Model {

  function insertRecord($record){
 
    if(count($record) > 0){
 
      // Check user
      $this->db->select('*');
      $this->db->where('username', $record[0]);
      $q = $this->db->get('users');
      $response = $q->result_array();
 
      // Insert record
      if(count($response) == 0){
        $newuser = array(
          "username" => trim($record[0]),
          "name" => trim($record[1]),
          "gender" => trim($record[2]),
          "email" => trim($record[3])
        );

        $this->db->insert('users', $newuser);
      }
 
    }
 
  }

}

---

4. Controller

Create a new Users.php in application/controllers/ directory.

Define two methods –

• __construct() – Load url helper and Main_model model.
• index() – On submit, button click check the file selected or not. If selected then set upload preference and load upload library.

Open the uploaded file in read mode.

I assigned the total number of columns in a row – 4 to $numberOfFields. Modify its value according to the number of columns in your CSV file.

Loop on the file and count total elements in $filedata Array and assign it to $num.

If $num == $numberOfFields then initialize $importData_arr Array.

To skip the first row created $skip variable and assigned 0 to it.

Loop on $importData_arr and call insertRecord() method if $skip != 0 to insert data in users Table where the pass the $userdata array.

Assign response to $data['response'] and load users_view view and pass $data.

Completed Code

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

class Users extends CI_Controller {

  public function __construct(){
    parent::__construct();

    // load base_url
    $this->load->helper('url');

    // Load Model
    $this->load->model('Main_model');
  }
 
  public function index(){

    // Check form submit or not 
    if($this->input->post('upload') != NULL ){ 
       $data = array(); 
       if(!empty($_FILES['file']['name'])){ 
         // Set preference 
         $config['upload_path'] = 'assets/files/'; 
         $config['allowed_types'] = 'csv'; 
         $config['max_size'] = '1000'; // max_size in kb 
         $config['file_name'] = $_FILES['file']['name'];

         // Load upload library 
         $this->load->library('upload',$config); 
 
         // File upload
         if($this->upload->do_upload('file')){ 
            // Get data about the file
            $uploadData = $this->upload->data(); 
            $filename = $uploadData['file_name'];

            // Reading file
            $file = fopen("assets/files/".$filename,"r");
            $i = 0;
            $numberOfFields = 4; // Total number of fields
            $importData_arr = array();
 
            while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
               $num = count($filedata );
               
               if($numberOfFields == $num){
                  for ($c=0; $c < $num; $c++) {
                     $importData_arr[$i][] = $filedata [$c];
                  }
               }
               $i++;
            }
            fclose($file);

            $skip = 0;

            // insert import data
            foreach($importData_arr as $userdata){

               // Skip first row
               if($skip != 0){
                  $this->Main_model->insertRecord($userdata);
               }
               $skip ++;
            }
            $data['response'] = 'successfully uploaded '.$filename; 
         }else{ 
            $data['response'] = 'failed'; 
         } 
      }else{ 
         $data['response'] = 'failed'; 
      } 
      // load view 
      $this->load->view('users_view',$data); 
    }else{
      // load view 
      $this->load->view('users_view'); 
    }

  }

}

---

5. View

Create a user_view.php file in application/views/ directory.

Create a <form > which contains a file element and a submit button. Use $response to display response either file import successfully or not.

Completed Code

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
 <head>
   <meta charset="utf-8">
 </head>
 <body>

   <?php 
   // Message
   if(isset($response)){
     echo $response;
   }
   ?>

   <form method='post' action='' enctype="multipart/form-data">
     <input type='file' name='file' >
     <input type='submit' value='Upload' name='upload'>
   </form>

  </body>
</html>

---

6. Conclusion

Using upload library to upload file to the server and insert records to the MySQL table while reading the file data.

You can also view this tutorial to know how to import CSV file data to the MySQL database in the CodeIgniter 4 project.

If you found this tutorial helpful then don't forget to share.