If you looking for web tutorial on exporting mysql data to CSV file in Codeignier. So, you have come on right place, in this post we have covered topic like export mysql data to CSV file in Codeigniter framework. Every body knows Codeigniter framework is used for boost you coding for make faster web based application. Once application has been make perfectly and end use your application completely, then you web application has large amount of data. Then at that time you have store that in readable file format. At that time you have to export your web application data in CSV (Comma-Seperated Values) file format. Because data inthis file format is widly used for importing and exporting data in web based application. And CSV file format is lightweight for store data in spread format.
Now question is arise How could you export mysql data in CSV file and download data in CSV file from live application in Codeigniter. First of all, we have already publish tutorial on how to import data from CSV file in Codeigniter framework. Now, In this post, we will discuss how to export data from Mysql database into CSV file using Codeigniter. Here also you can learn how to create CSV file in Codeigniter and download into local computer or Save Mysql data into CSV file in Codeigniter.
For describe Mysql data export to CSV file feature, we have make script in Codeigniter MVC framework, in which export student data from Mysql database table and insert into CSV file using Codeigniter.
See Also
Make Table in Mysql Database
Below script will make student table in your Mysql database. You have to run follow script in 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;
Make Database Connection in Codeigniter
Once student table has been ready, then you have to make database connection in Codeigniter, for database connection in Codeigniter, you have to go to application/config/database.php. In this file you have make 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 making database connection, you have to create Export_csv.php file in application/controllers folder. Controllers files mainly used for handle HTTP request of application. In this file we have to make follow method.
index() - This method is a root method of this class, this method will received student data from model and send data to view file.
export() - This method is received form submission request for export mysql data to CSV file. This function will write mysql data under CSV file and after file send 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 files in Codeigniter mainly used for database operation. You have to create models file in application/models folder. In this file there is only one method we have make. Here fetch_data() method is used for fetch data from 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 for display output in HTML format on web page. This file you have to make in application/views folder. This file first received student data from controllers, and when used click on export button, then it has received data export to CSV request for export data to 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>
This tutorial will you help to learn exporting of Mysql Data to CSV file format in Codeigniter.
Thanks for such a nice tutorial. i always look for your solution for web related problems.
ReplyDeleteThis site can’t be reached error when I click to import
ReplyDeleteGreat work! How about an import CSV tutorial?
ReplyDelete