Export MySQL data to CSV file in CodeIgniter

CSV (Comma Separated Values ) is the most popular file format for data import and export within the project.

In PHP for creating CSV file, you can either use fputcsv() method or directly write the comma-separated content on the file.

In this tutorial, I will show you how you can export MySQL data in CSV file format in your CodeIgniter 3 project.

Export MySQL data to CSV file in CodeIgniter 3

Demo Download

Table structure
Configuration
Model
Controller
View
Demo
Conclusion

1. Table structure

I am using users table in the example and added some data.

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

$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/ folder.

Create a single method getUserDetails() for records select from users table and return an Array.

Completed Code

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

class Main_model extends CI_Model {

  function getUserDetails(){
 
    $response = array();
 
    // Select record
    $this->db->select('username,name,gender,email');
    $q = $this->db->get('users');
    $response = $q->result_array();
 
    return $response;
  }

}

4. Controller

Create a new Users.php file in application/controller/ folder.

Defined 3 methods –

__constuct() – Load url helper and Main_model model.
index() – Fetch data by calling getUsersDetails() method and pass in $this->load->view('users_view',$data);
exportCSV() – Using this method for export data.

Set the filename and header requests. Select records by calling getUserDetails() method and create a file variable.

Loop on the fetched records and write data in $file variable using fputcsv() method.

Completed Code

<?phpdefined('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(){
   // get data $data = array(); 
   $data['usersData'] = $this->Main_model->getUserDetails();
  
   // load view 
   $this->load->view('users_view',$data); 
  }
 
  // Export data in CSV format 
  public function exportCSV(){ 
   // file name 
   $filename = 'users_'.date('Ymd').'.csv'; 
   header("Content-Description: File Transfer"); 
   header("Content-Disposition: attachment; filename=$filename"); 
   header("Content-Type: application/csv; ");
   
   // get data 
   $usersData = $this->Main_model->getUserDetails();

   // file creation 
   $file = fopen('php://output', 'w');
 
   $header = array("Username","Name","Gender","Email"); 
   fputcsv($file, $header);
   foreach ($usersData as $key=>$line){ 
     fputcsv($file,$line); 
   }
   fclose($file); 
   exit; 
  }
}

5. View

Create a new users_view.php file in application/views/ folder.

List user data in <table> by looping on $usersData array variable and create a <a href='<?= base_url() ?>index.php/users/exportCSV'>Export</a> element for an export link.

Completed Code

<?php

defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
 <head> 
   <meta charset="utf-8"> 
   <title>Export MySQL data to CSV file in CodeIgniter 3</title>
 </head>
 <body>
   <!-- Export Data --> 
   <a href='<?= base_url() ?>index.php/users/exportCSV'>Export</a><br><br>

   <!-- User Records --> 
   <table border='1' style='border-collapse: collapse;'> 
     <thead> 
      <tr> 
       <th>Username</th> 
       <th>Name</th> 
       <th>Gender</th> 
       <th>Email</th> 
      </tr> 
     </thead> 
     <tbody> 
     <?php
     foreach($usersData as $key=>$val){ 
       echo "<tr>"; 
       echo "<td>".$val['username']."</td>"; 
       echo "<td>".$val['name']."</td>"; 
       echo "<td>".$val['gender']."</td>"; 
       echo "<td>".$val['email']."</td>"; 
       echo "</tr>"; 
      } 
      ?> 
     </tbody> 
    </table>
  </body>
</html>

6. Demo

View Demo

7. Conclusion

In the example, I created a method for the data export in CSV format and pointed the Export link to the controller method. I used fputcsv() method to write data to the file.

You can view the CodeIgniter 4 version of this tutorial here.

View this tutorial, If you want to know CSV file data import in CodeIgniter 3.

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

Contents

1. Table structure

2. Configuration

3. Model

4. Controller

5. View

6. Demo

7. Conclusion