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 project.
Contents
- 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/
directory.
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/
directory.
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 file varialbe.
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/
directory.
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</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. Conclusion
Click on the Export link.
7. Conclusion
In the demonstration, 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.