This tutorial is shows how to export data into csv file using codeigniter. Below are the step by step implementation to download mysql data into csv file.
This tutorial uses employee table :
employee mysql table:
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`status` enum('A','D') NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Table structure and data looks like :
Employee Details View
<!DOCTYPE html>
<html lang="en">
<head>
<title>Employee Listing</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<form name="export_data" id="export_data" method="post" action="<?php echo base_url("employee/export_csv"); ?>">
<div class="container">
<h2>Employee Listing</h2>
<table class="table table-bordered">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Age</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
if (isset($employee_details) && !empty($employee_details)) {
foreach ($employee_details as $employee) {
?>
<tr>
<td><?php echo $employee->id; ?></td>
<td><?php echo $employee->first_name; ?></td>
<td><?php echo $employee->last_name; ?></td>
<td><?php echo $employee->email; ?></td>
<td><?php echo $employee->age; ?></td>
<td><?php
if ($employee->status == "A")
echo "Active";
else
"Inactive";
?></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
<button class="btn btn-primary pull-right">Export</button>
</div>
</form>
</body>
</html>
This view displays the employee details in table format.
Employee Controller:
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Employee extends CI_Controller {
public $page_data;
public function __construct() {
parent::__construct();
$this->load->model('employee_m');
}
public function listing() {
$this->page_data['employee_details'] = $this->employee_m->get();
$this->load->view('display_employee', $this->page_data);
}
public function export_csv() {
$this->load->helper('csv');
$export_arr = array();
$employee_details = $this->employee_m->get();
$title = array("Id", "First Name", "Last Name", "Email", "Age", "Status");
array_push($export_arr, $title);
if (!empty($employee_details)) {
foreach ($employee_details as $employee) {
$status = $employee->status == "A" ? "Active" : "Inactive";
array_push($export_arr, array($employee->id, $employee->first_name, $employee->last_name, $employee->email, $employee->age, $status));
}
}
convert_to_csv($export_arr, 'employee' . date('F d Y') . '.csv', ',');
}
}
This controller has two methods: 1] listing method is used to fetch data from mysql table and display it on browser. 2] export_csv function is used to export the data into csv. After clicking on export button this function gets executed. For exporting data, here csv helper is used.
Employee_m model:
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Employee_m extends CI_Model {
public function get() {
$sql = "SELECT * FROM employee";
$q = $this->db->query($sql);
if ($q->num_rows() > 0) {
foreach ($q->result() as $row) {
$data[] = $row;
}
return $data;
}
}
}
This model is used for getting employee data from mysql table and passes that data to the controller.
CSV Helper :
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
if (!function_exists('convert_to_csv')) {
function convert_to_csv($input_array, $output_file_name, $delimiter) {
/** open raw memory as file, no need for temp files */
$temp_memory = fopen('php://memory', 'w');
/** loop through array */
foreach ($input_array as $line) {
/** default php csv handler * */
fputcsv($temp_memory, $line, $delimiter);
}
/** rewrind the "file" with the csv lines * */
fseek($temp_memory, 0);
/** modify header to be downloadable csv file * */
header('Content-Type: application/csv');
header('Content-Disposition: attachement; filename="' . $output_file_name . '";');
/** Send file to browser for download */
fpassthru($temp_memory);
}
}
This is the final output you see in csv file.
This tutorial may be helpful for you:)