In this tutorial, you will learn how to export the data from MySQL to CSV file using PHP CodeIgniter. CSV(comma-separated values) is the most popular file format to store tabular data in plain text. Using CSV file you can import a huge amount of data in the MySQL database. CSV file stores the data in a comma-separated format, it is easy to maintain the records in the CSV file. 
The below example demonstrates how to import records in MySQL using PHP CodeIgniter. 

Click on the Live Demo button to see the output.
We have used the employees table in this demo, below is the structure of the employees table in the MySQL database.

Create Table in MySql (Phpmyadmin)

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `email` varchar(80) NOT NULL,
  `username` varchar(45) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `bio` text,
  `profile_pic` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`name`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert some dummy data in the employees table, so that we can list the records on the webpage.

Before getting started to implement the export CSV file from the database in CodeIgniter  application, take a look at the files structure.

codeigniter_csv_export
 |
 |____ applications/
 |         |
 |         |______controllers/
 |         |             |
 |         |             |_______Employees_Controller.php
 |         |
 |         |______helpers/
 |         |            |
 |         |            |_______csv_helper.php
 |         |
 |         |_______models/
 |         |             |
 |         |             |_______Employees_model.php
 |         |
 |         |______viwes/
 |                       |
 |                       |_______display_employee.php
         

Now create the employees controller so that we can write the logic to list the employees records on the webpage and export records into the CSV file. Below is the Employees_Controller.php

Employees_Controller.php (application/controllers/Employees_Controller.php)

<?php

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

class Employees_Controller extends CI_Controller {

    public $page_data;

    public function __construct() {
        parent::__construct();
        $this->load->model('employees_model');
    }

    public function listing() {
        $this->page_data['employee_details'] = $this->employees_model->get();
        $this->load->view('display_employee', $this->page_data);
    }

    public function export_csv() {
        $this->load->helper('csv');
        $export_arr = array();
        $employee_details = $this->employees_model->get();
        $title = array("Id", "Name", "Email", "Phone", "Created at");
        array_push($export_arr, $title);
        if (!empty($employee_details)) {
            foreach ($employee_details as $employee) {
                array_push($export_arr, array($employee->id, $employee->name, $employee->email, $employee->mobile, $employee->created_at));
            }
        }
        convert_to_csv($export_arr, 'employee-' . date('F d Y') . '.csv', ',');
    }

}

In Employees_Controller we have two functions, listing() function is used to list all the employees on the webpage and export_csv() function is used to export the records in CSV

Now create the Employees_model to get all the employees from the employees table.

Employees_model.php (application/models/Employees_model.php)

<?php

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

class Employees_model extends CI_Model {

    public function get() {
        $sql = "SELECT * FROM employees";
        $q = $this->db->query($sql);
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $data[] = $row;
            }
            return $data;
        }
    }

}

Now create the display_employee View to list all the employees on the webpage.

display_employee.php (application/views/display_employee.php)

<!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("Employees_Controller/export_csv"); ?>">
            <div class="container">
                <h2>Employee Listing</h2>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Id</th>
                            <th>Name</th>                       
                            <th>Email</th>
                            <th>Phone</th>
                            <th>Created at</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->name; ?></td>
                                    <td><?php echo $employee->email; ?></td>
                                    <td><?php echo $employee->mobile; ?></td>
                                    <td><?php echo $employee->created_at; ?></td>                                    
                                </tr>
                                <?php
                            }
                        }
                        ?>
                    </tbody>
                </table>
                <button class="btn btn-primary pull-right">Export</button>
            </div>
        </form>
    </body>
</html>


Now create the csv_helper file to manipulate with the CSV file, We have loaded the CSV helper inside the export_csv() function like this
 $this->load->helper('csv');

csv_helper.php (application/helpers/csv_helper.php)

<?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);
    }

}


Now click on the view Live Demo button and click on the Export button on the demo page, to see the export csv output.
Written by
Check out this Author's contributed articles.
If you like Tutorialsmint and would like to contribute, you can also write an article using Write an Article. See your article appearing on the Tutorialsmint main page and help other programmers to enhance their knowledge.

Comments(0)

RECENT TUTORIALS
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Edit Post
Add Reply