Export Query Results as CSV File Using Codeigniter

Codeigniter

Dear friends,

In codeigniter, there is a database utilities class to export MySQL database data as CSV File. In order to initialize the utility class, ensure that the database class is running in your codeigniter application. F

Friends, follow these steps to export query results as CSV file using your codeigniter application.

Step 1: Download your codeigniter application and route your page.

step 2: Set database username,password and db name in application/config/database.php file.

Step 3: Create a table and insert data to export data s csv file from your MySQL database.

--
-- Table structure for table `csv`
--

CREATE TABLE IF NOT EXISTS `csv` (
  `id` int(1) NOT NULL,
  `name` varchar(20) NOT NULL,
  `email` varchar(70) NOT NULL,
  `phone` int(10) NOT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `csv`
--

INSERT INTO `csv` (`id`, `name`, `email`, `phone`, `status`) VALUES
(1, 'John', 'john@gamil.com', 987654321, 1),
(2, 'Smith', 'smith@gmail.com', 987654321, 1),
(4, 'Anson', 'anson@gamil.com', 987654321, 1),
(5, 'Cinan', 'cinan@gmail.com', 987654321, 1);

--
-- Indexes for dumped tables
--

Step 4 : Create a controller under application/controllers/. Name it as Export.php.

Step 5 : Please copy below code to Export.php file and save it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?php defined('BASEPATH') OR exit('No direct script access allowed'); 
class Export extends CI_Controller { 
    function __construct() { 
         parent::__construct(); 
    } 
    public function index() { 
         $this->load->view('csv/csv_v');
    }
     public function exportData()
	{
		
		$data   = [];
		$this->db->select('name,email,phone');
		$query  =	$this->db->get('csv');
		$result	=	$query->result_array();
		if(isset($result)){

			$data['export']	=	$result;
		}
		if(isset($_POST['import']))
		{
	            $query  =	$this->db->get('csv');
		    if($query->num_rows() > 0){

			$path ="";
			header("Content-Description: File Transfer"); 
		        header("Content-Disposition: attachment; filename=$path"); 
		        header("Content-Type: application/csv; ");
			$this->load->dbutil();
			$delimiter = ",";
	                $newline = "\r\n";
	                $data = $this->dbutil->csv_from_result($query, $delimiter, 
$newline);
			 $this->load->helper('download');
			 force_download('CSV_Report.csv', $data);
                     }			
		 }
		$this->load->view('csv/export_v',$data);
	}
}

$this->load->dbutil(); - loads database utility class.
csv_from_result($query,$delimiter,$newline) - converst the Query results to csv file.
First parameter specifies the query to generate the result. Second parameter specifies
delimiter to separate the resultant data. Thrid parameter allows you to set the newline.

This database utility function generates a csv layout. So to generate and download a CSV file use force_download() function.
force_download($file) – Download the file.
Step 6: Create the view page  in  application/views directory to export the data from mysql database to csv file on a button click. Name the file as export_v.php

Step 7: Copy and paste the code to your view file and  save it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<html>
 <head>
 <title></title> 
 <!-- Latest compiled and minified CSS -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/
bootstrap.min.css">
 <!-- jQuery library -->
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js">
</script>
 <!-- Latest compiled JavaScript -->
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js">
</script>
  <style type="text/css">
      .container{  
          margin-top: 50px; border:1px solid rgba(158, 158, 158, 0.49);
          padding-bottom: 50px;
      }
      h3{ 
           text-align: center; 
      }  
      .top { } 
   </style>
 </head>
 <body>
      <div class="container">
          <div class="row">
              <h3>Import MYSQL data to CSV File</h3>
          </div>
          <div class="top">
              <form method="post">
                  <button type="submit" class="btn btn-primary" name="export">Export
</button> 
              </form>
          </div> 
          <table class="table"> 
              <tr>
                    <th>Name</th><th>Email</th><th>Phone</th>
              </tr>
               <?php if(isset($export)) { foreach($export as $ind => $value) {?> 
                   <tr> 
                        <td><?= $value['name'] ?></td> 
                        <td><?= $value['email'] ?></td> 
                        <td><?= $value['phone'] ?></td>
                  </tr> 
               <?php } } ?> 
           </table> 
      </div> 
  </body>
</html> 

Step 8: Open browser and type url to view the site.

Leave a Reply

Your email address will not be published. Required fields are marked *