How to Import Data in MySQL with CodeIgniter

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, their is upload library available which makes easier to handle file uploads.

In this tutorial, I import data of CSV file in MySQL database table with CodeIgniter.

How to Import Data in MySQL with CodeIgniter


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 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.

Read the file after successfully CSV file uploading and initialize $importData_arr.

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

Load users_view view.

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;

            $importData_arr = array();
 
            while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
               $num = count($filedata );

               for ($c=0; $c < $num; $c++) {
                  $importData_arr[$i][] = $filedata [$c];
               }
               $i++;
            }
            fclose($file);

            $skip = 0;

            // insert import data
            foreach($importData_arr as $userdata){
               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 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 upload file to the server and insert records to the MySQL table while reading the file data.

If you like this post then don’t forget to share.

Related Post

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *