How To Export Mysql Data to CSV File In Codeigniter

If you are looking for a web tutorial on exporting MySQL data to a CSV file in Codeignier. To get you in the right place, we covered topics in this publication such as exporting MySQL data to a CSV file under Codeigniter. Everyone knows that the Codeigniter framework is used to drive coding and build a faster web-based application. Once the application runs perfectly and its final use is complete, your web application contains a lot of data. Then you have saved it in a readable file format at this point. At this point you need to export your web application data in CSV file format (comma separated values). Because the data in this file format is often used to import and export data in web-based applications. The CSV file format is very simple for storing data in the extended format.

Now the question arises: How can I export MySQL data to a CSV file and download data to a CSV file from the live application in Codeigniter? First of all, we have already published a tutorial on importing data from a CSV file under Codeigniter. This article will now explain how data from the MySQL database can be exported to a CSV file using code signiter. Here you will also learn how to create a CSV file in Codeigniter and download it to a local computer or save MySQL data in a CSV file in Codeigniter.


In order to describe the export of Mysql data to the CSV file function, we have created a script in the Codeigniter MVC framework in which you export student data from the Mysql database table and insert it into the CSV file with Codeigniter.

Make Table in Mysql Database

The following script creates the student table in your MySQL database. You need to run the tracking script on your PHPMyAdmin.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_student`
--

CREATE TABLE `tbl_student` (
  `student_id` int(11) NOT NULL,
  `student_name` varchar(250) NOT NULL,
  `student_phone` varchar(20) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_student`
--
ALTER TABLE `tbl_student`
  ADD PRIMARY KEY (`student_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_student`
--
ALTER TABLE `tbl_student`
  MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Create Database Connection

Once the student table is ready, you need to establish the database connection in Codeigniter. For the database connection in Codeigniter you have to go to application / config / database.php. In this file you have to establish the database connection in Codeigniter.

application/config/database.php

<?php

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'testing',
 '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
);

?>

Export_csv.php (Controllers)

After establishing the connection to the database you have to create the file Export_csv.php in the folder application / controller. Driver files used primarily to process the application's HTTP request. In this file we have to do the following method.

index () - This method is a root method of this class. This method receives data from the model student and sends data to view the file. export (): This method receives a form submission request to export MySQL data to a CSV file. This function writes MySQL data into a CSV file and after sending the file for download.


application/controllers/Export_csv.php

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

class Export_csv extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('export_csv_model');
 }

 function index()
 {
  $data['student_data'] = $this->export_csv_model->fetch_data();
  $this->load->view('export_csv', $data);
 }

 function export()
 {
  $file_name = 'student_details_on_'.date('Ymd').'.csv'; 
     header("Content-Description: File Transfer"); 
     header("Content-Disposition: attachment; filename=$file_name"); 
     header("Content-Type: application/csv;");
   
     // get data 
     $student_data = $this->export_csv_model->fetch_data();

     // file creation 
     $file = fopen('php://output', 'w');
 
     $header = array("Student Name","Student Phone"); 
     fputcsv($file, $header);
     foreach ($student_data->result_array() as $key => $value)
     { 
       fputcsv($file, $value); 
     }
     fclose($file); 
     exit; 
 }
 
  
}

Export_csv_model.php (Models)

Models of codeigniter files used primarily for database operations. You need to create the model file in the application / models folder. There is only one method in this file that we created. Here the method fetch_data () is used to get data from the student table.

<?php
class Export_csv_model extends CI_Model
{
 function fetch_data()
 {
  $this->db->select("student_name, student_phone");
  $this->db->from('tbl_student');
  return $this->db->get();
 }
}

?>

export_csv.php (Views)

This view file is used to display results in HTML format on the website. This file must be created in the applications / views folder. This file first received the student data from the controllers. Click the "Export" button when in use, and then you will be prompted to export data to CSV to export the data to the CSV file.

<html>
<head>
    <title>How To  Export Mysql Data to CSV File In Codeigniter</title>
    
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    
</head>
<body>
 <div class="container box">
  <h3 align="center">How To  Export Mysql Data to CSV File In Codeigniter</h3>
  <br />
  <form method="post" action="<?php echo base_url(); ?>export_csv/export">
   <div class="panel panel-default">
    <div class="panel-heading">
     <div class="row">
      <div class="col-md-6">
       <h3 class="panel-title">Student Data</h3>
      </div>
      <div class="col-md-6" align="right">
       <input type="submit" name="export" class="btn btn-success btn-xs" value="Export to CSV" />
      </div>
     </div>
    </div>
    <div class="panel-body">
     <div class="table-responsive">
      <table class="table table-bordered table-striped">
       <tr>
        <th>Student Name</th>
        <th>Student Phone</th>
       </tr>
       <?php
       foreach($student_data->result_array() as $row)
       {
        echo '
        <tr>
         <td>'.$row["student_name"].'</td>
         <td>'.$row["student_phone"].'</td>
        </tr>
        ';
       }
       ?>
      </table>
     </div>
    </div>
   </div>
  </form>
 </div>
</body>
</html>

In this tutorial, you will learn how to export MySQL data in Codeigniter to the CSV file format.