Import/Export Data to CSV file with CodeIgniter and MySQL
CSV stands for "comma-separated values". Its data fields are most often separated, or delimited, by a comma. The CSV format is the most popular file format to use for data export and import functionality.Import/Export data functionality makes your web application user-friendly and helps the user to maintain list data. In this post, We have share how to implement Import/Export Data to CSV file with CodeIgniter and MySQL. We have provided full functional demo and download also.
Step 1: Create MySQL Database and Table
The following SQL creates a customer table in the MySQL database.
Step 2: Create file
Create a file named CSVReader.php inside “application/libraries” folder.
Step 3: The Customer controller handles the CSV data import process.
Create a file named Customer.php inside “application/controllers folder.
Step 4: The Customer model handles the database .
Create a file named Customer_model.php inside “application/models folder.
Step 5: Create a view file index
Create a view file named “index.php” inside “application/views/customer folder
Step 6: Create a view file add
Create a view file named add.php” inside “application/views/customer folder
Demo [sociallocker] Download[/sociallocker]
Step 1: Create MySQL Database and Table
The following SQL creates a customer table in the MySQL database.
-- Table structure for table `customer`
CREATE TABLE `customer` (
`customer_id` int(11) NOT NULL,
`firstname` varchar(32) NOT NULL,
`lastname` varchar(32) NOT NULL,
`email` varchar(96) NOT NULL,
`phone` varchar(32) NOT NULL,
`status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Dumping data for table `customer`
INSERT INTO `customer` (`customer_id`, `firstname`, `lastname`, `email`, `phone`, `status`) VALUES
(1, 'Nixon', 'Tiger', 'tiger@techarise.com', '9000000001', 1),
(2, 'Garrett', 'Winters', 'winters@techarise.com', '9000000002', 1),
(3, 'Ashton', 'Cox', 'cox@techarise.com', '9000000003', 1),
(4, 'Cedric', 'Kelly', 'kelly@techarise.com', '9000000004', 1),
(5, 'Airi', 'Satouy', 'airi@techarise.com', '9000000005', 1),
(6, 'Brielle', 'Williamson', 'will@techarise.com', '9000000006', 1),
(7, 'Herrod', 'Chandler', 'herrod@techarise.com', '9000000007', 1),
(8, 'Rhona ', 'Davidson', 'rd@techarise.com', '9000000008', 1),
(9, 'Colleen', 'Hurst', 'colleen@techarise.com', '9000000009', 1),
(10, 'Sonya', 'Frost', 'frost@techarise.com', '9000000010', 1);
-- Indexes for table `customer`
ALTER TABLE `customer`
ADD PRIMARY KEY (`customer_id`);
-- AUTO_INCREMENT for table `customer`
ALTER TABLE `customer`
MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
?>
Step 2: Create file
Create a file named CSVReader.php inside “application/libraries” folder.
if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
* ==============================
* CSVReader
*
* @package : CodeIgniter 3.x
* @category : Libraries
* @version : 1.0
* @author : TechArise
* ==============================
*/
class CSVReader {
// columns names retrieved after parsing
private $fields;
// separator used to explode each line
private $separator = ';';
// enclosure used to decorate each field
private $enclosure = '"';
// maximum row size to be used for decoding
private $max_row_size = 4096;
function parse_csv($filepath){
// Checking whether a file exists or not
if(!file_exists($filepath)){
return FALSE;
}
// The fopen() function opens a file or URL.
$csvFileName = fopen($filepath, 'r');
// Get Fields and values
$this->fields = fgetcsv($csvFileName, $this->max_row_size, $this->separator, $this->enclosure);
$keys_values = explode(',', $this->fields[0]);
$keys = $this->escape_string($keys_values);
// Store CSV data in an array
$csvData = array();
$count = 1;
while(($row = fgetcsv($csvFileName, $this->max_row_size, $this->separator, $this->enclosure)) !== FALSE){
// Skip empty lines
if($row != NULL){
$values = explode(',', $row[0]);
if(count($keys) == count($values)){
$arr = array();
$new_values = array();
$new_values = $this->escape_string($values);
for($j = 0; $j < count($keys); $j++){
if($keys[$j] != ""){
$arr[$keys[$j]] = $new_values[$j];
}
}
$csvData[$count] = $arr;
$count++;
}
}
}
// The fclose() function closes an open file.
fclose($csvFileName);
return $csvData;
}
// escape string
function escape_string($data){
$result = array();
foreach($data as $row){
$result[] = str_replace('"', '', $row);
}
return $result;
}
}
?>
Step 3: The Customer controller handles the CSV data import process.
Create a file named Customer.php inside “application/controllers folder.
/**
* Description of Customer Controller: CodeIgniter
*
* @author TechArise Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Customer extends CI_Controller {
public function __construct() {
parent::__construct();
//load model
$this->load->model('Customer_model', 'customer');
// load pagination library
$this->load->library('pagination');
// Load form validation library
$this->load->library('form_validation');
// load CSV library
$this->load->library('CSVReader');
// Load file helper
$this->load->helper('file');
}
// list customer
public function index() {
$data = array();
$data['page'] = 'customer-list';
$data['title'] = 'Customer Info | TechArise';
$data['breadcrumbs'] = array('Home' => '#');
$this->customer->setStatus(1);
$config['total_rows'] = $this->customer->countCustomer();
$page_number = $this->uri->segment(2);
$config['base_url'] = base_url() . 'customer';
if (empty($page_number))
$page_number = 1;
$offset = ($page_number - 1) * $this->pagination->per_page;
$this->customer->setPageNumber($this->pagination->per_page);
$this->customer->setOffset($offset);
$this->pagination->cur_page = $offset;
$this->pagination->initialize($config);
$data['page_links'] = $this->pagination->create_links();
$data['customerInfo'] = $this->customer->getcustomerList();
$this->load->view('customer/index', $data);
}
// add customer
public function add() {
$data = array();
$data['page'] = 'customer-add';
$data['title'] = 'Customer Add | TechArise';
$data['breadcrumbs'] = array('Home' => '#');
$this->load->view('customer/add', $data);
}
// save
public function save() {
$this->form_validation->set_rules('fileURL', 'Upload File', 'callback_checkFileValidation');
if($this->form_validation->run() == false) {
$data = array();
$data['page'] = 'customer-add';
$data['title'] = 'Customer Add | TechArise';
$data['breadcrumbs'] = array('Home' => '#');
$this->load->view('customer/add', $data);
} else {
// If file uploaded
if(is_uploaded_file($_FILES['fileURL']['tmp_name'])) {
// Parse data from CSV file
$csvData = $this->csvreader->parse_csv($_FILES['fileURL']['tmp_name']);
// create array from CSV file
if(!empty($csvData)){
foreach($csvData as $element){
// Prepare data for Database insertion
$data[] = array(
'firstname' => $element['FirstName'],
'lastname' => $element['LastName'],
'email' => $element['Email'],
'phone' => $element['Phone'],
'status' => $element['Status'],
);
}
}
}
// insert/update data into database
foreach($data as $element) {
$this->customer->setFirstName($element['firstname']);
$this->customer->setLastName($element['lastname']);
$this->customer->setEmail($element['email']);
$this->customer->setPhone($element['phone']);
$this->customer->setStatus($element['status']);
$this->customer->createCustomer();
}
redirect('customer');
}
}
// checkFileValidation
public function checkFileValidation($string) {
$mime_types = array(
'text/csv',
'text/x-csv',
'application/csv',
'application/x-csv',
'application/excel',
'text/x-comma-separated-values',
'text/comma-separated-values',
'application/octet-stream',
'application/vnd.ms-excel',
'application/vnd.msexcel',
'text/plain',
);
if(isset($_FILES['fileURL']['name']) && $_FILES['fileURL']['name'] != ""){
// get mime by extension
$mime = get_mime_by_extension($_FILES['fileURL']['name']);
$fileExt = explode('.', $_FILES['fileURL']['name']);
$ext = end($fileExt);
if(($ext == 'csv') && in_array($mime, $mime_types)){
return true;
}else{
$this->form_validation->set_message('checkFileValidation', 'Please choose correct file.');
return false;
}
}else{
$this->form_validation->set_message('checkFileValidation', 'Please choose a file.');
return false;
}
}
// export Data
public function exportData() {
$storData = array();
$metaData[] = array('firstname' => 'FirstName', 'lastname' => 'LastName', 'email' => 'Email', 'phone' => 'Phone', 'status' => 'Status');
$this->customer->setStatus(1);
$customerInfo = $this->customer->getcustomerList();
foreach($customerInfo as $key=>$element) {
$storData[] = array(
'firstname' => $element['firstname'],
'lastname' => $element['lastname'],
'email' => $element['email'],
'phone' => $element['phone'],
'status' => $element['status'],
);
}
$data = array_merge($metaData,$storData);
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=\"csv-sample-customer".".csv\"");
header("Pragma: no-cache");
header("Expires: 0");
$handle = fopen('php://output', 'w');
foreach ($data as $data) {
fputcsv($handle, $data);
}
fclose($handle);
exit;
}
}
?>
Step 4: The Customer model handles the database .
Create a file named Customer_model.php inside “application/models folder.
/**
* Description of Customer Model: CodeIgniter
*
* @author TechArise Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Customer_model extends CI_Model {
private $_customerID;
private $_firstname;
private $_lastname;
private $_email;
private $_phone;
private $_status;
private $_limit;
private $_pageNumber;
private $_offset;
public function setCustomerID($customerID) {
$this->_customerID = $customerID;
}
public function setFirstName($firstname) {
$this->_firstname = $firstname;
}
public function setLastName($lastname) {
$this->_lastname = $lastname;
}
public function setEmail($email) {
$this->_email = $email;
}
public function setPhone($phone) {
$this->_phone = $phone;
}
public function setStatus($status) {
$this->_status = $status;
}
public function setLimit($limit) {
$this->_limit = $limit;
}
public function setPageNumber($pageNumber) {
$this->_pageNumber = $pageNumber;
}
public function setOffset($offset) {
$this->_offset = $offset;
}
// count Customer
public function countCustomer() {
$this->db->where('status', $this->_status);
$this->db->from('customer');
return $this->db->count_all_results();
}
// get customer List
public function getcustomerList() {
$this->db->select(array('c.customer_id', 'c.firstname', 'c.lastname', 'c.email', 'c.phone', 'c.status'));
$this->db->from('customer as c');
$this->db->where('c.status', $this->_status);
if(!empty($this->_pageNumber)) {
$this->db->limit($this->_pageNumber, $this->_offset);
}
$query = $this->db->get();
return $query->result_array();
}
// create Customer
public function createCustomer() {
$tableName = 'customer';
$this->db->select(array('c.customer_id'));
$this->db->from($tableName . ' as c');
$this->db->where('c.email', $this->_email);
$query = $this->db->get();
if ($query->num_rows() > 0) {
$data = array(
'firstname' => $this->_firstname,
'lastname' => $this->_lastname,
'phone' => $this->_phone,
'status' => $this->_status,
);
$this->db->where('email', $this->_email);
$this->db->update($tableName, $data);
} else {
$data = array(
'firstname' => $this->_firstname,
'lastname' => $this->_lastname,
'email' => $this->_email,
'phone' => $this->_phone,
'status' => $this->_status,
);
$this->db->insert($tableName, $data);
return $this->db->insert_id();
}
}
}
?>
Step 5: Create a view file index
Create a view file named “index.php” inside “application/views/customer folder
load->view('templates/header'); ?>
0) {?>
Import/Export CSV File Data into MySQL Database in CodeIgniter
First Name
Last Name
Phone
$grandTotal =0;
foreach($customerInfo as $key=>$element) { ?>
There is no result yet.
0) echo $page_links; ?>
load->view('templates/footer'); ?>
?>
Step 6: Create a view file add
Create a view file named add.php” inside “application/views/customer folder
load->view('templates/header'); ?>
load->view('templates/footer'); ?>
?>
Demo [sociallocker] Download[/sociallocker]
Komentar
Posting Komentar