CSV files are easier to maintain and can be used for export and import data in the web application.
For data import first, need to allow the users to browse and upload the file. In CodeIgniter, there is upload library available that makes it easier to handle file uploads.
In this tutorial, I show how you can import data of CSV file in the MySQL database table in the CodeIgniter 3 project.
Contents
1. Table structure
I am using users table in the demonstration.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(60) NOT NULL, `name` varchar(50) NOT NULL, `gender` varchar(10) NOT NULL, `email` varchar(60) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Navigate to application/config/database.php and define the Database connection.
$db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', // Username 'password' => '', // Password 'database' => 'tutorial', // Database name '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 );
Default controller
Open application/config/routes.php and edit default_controller value to Users.
$route['default_controller'] = 'Users';
Load Database
To access the MySQL database require loading database library.
Open application/config/autoload.php and add the database in libraries array().
$autoload['libraries'] = array("database");
3. Model
Create a new Main_model.php file in application/models/ directory.
Define insertRecord() method to insert record in users table. It takes an Array as a parameter.
Check user already exit or not by username if not then define an Array $newuser and pass in $this->db->insert('users',$newuser).
Completed Code
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Main_model extends CI_Model {
function insertRecord($record){
if(count($record) > 0){
// Check user
$this->db->select('*');
$this->db->where('username', $record[0]);
$q = $this->db->get('users');
$response = $q->result_array();
// Insert record
if(count($response) == 0){
$newuser = array(
"username" => trim($record[0]),
"name" => trim($record[1]),
"gender" => trim($record[2]),
"email" => trim($record[3])
);
$this->db->insert('users', $newuser);
}
}
}
}
4. Controller
Create a new Users.php in application/controllers/ directory.
Define two methods –
- __construct() – Load
urlhelper andMain_modelmodel. - index() – On submit, button click check the file selected or not. If selected then set upload preference and load
uploadlibrary.
Open the uploaded file in read mode.
I assigned the total number of columns in a row – 4 to $numberOfFields. Modify its value according to the number of columns in your CSV file.
Loop on the file and count total elements in $filedata Array and assign it to $num.
If $num == $numberOfFields then initialize $importData_arr Array.
To skip the first row created $skip variable and assigned 0 to it.
Loop on $importData_arr and call insertRecord() method if $skip != 0 to insert data in users Table where the pass the $userdata array.
Assign response to $data['response'] and load users_view view and pass $data.
Completed Code
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Users extends CI_Controller {
public function __construct(){
parent::__construct();
// load base_url
$this->load->helper('url');
// Load Model
$this->load->model('Main_model');
}
public function index(){
// Check form submit or not
if($this->input->post('upload') != NULL ){
$data = array();
if(!empty($_FILES['file']['name'])){
// Set preference
$config['upload_path'] = 'assets/files/';
$config['allowed_types'] = 'csv';
$config['max_size'] = '1000'; // max_size in kb
$config['file_name'] = $_FILES['file']['name'];
// Load upload library
$this->load->library('upload',$config);
// File upload
if($this->upload->do_upload('file')){
// Get data about the file
$uploadData = $this->upload->data();
$filename = $uploadData['file_name'];
// Reading file
$file = fopen("assets/files/".$filename,"r");
$i = 0;
$numberOfFields = 4; // Total number of fields
$importData_arr = array();
while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
$num = count($filedata );
if($numberOfFields == $num){
for ($c=0; $c < $num; $c++) {
$importData_arr[$i][] = $filedata [$c];
}
}
$i++;
}
fclose($file);
$skip = 0;
// insert import data
foreach($importData_arr as $userdata){
// Skip first row
if($skip != 0){
$this->Main_model->insertRecord($userdata);
}
$skip ++;
}
$data['response'] = 'successfully uploaded '.$filename;
}else{
$data['response'] = 'failed';
}
}else{
$data['response'] = 'failed';
}
// load view
$this->load->view('users_view',$data);
}else{
// load view
$this->load->view('users_view');
}
}
}
5. View
Create a user_view.php file in application/views/ directory.
Create a <form > which contains a file element and a submit button. Use $response to display response either file import successfully or not.
Completed Code
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
</head>
<body>
<?php
// Message
if(isset($response)){
echo $response;
}
?>
<form method='post' action='' enctype="multipart/form-data">
<input type='file' name='file' >
<input type='submit' value='Upload' name='upload'>
</form>
</body>
</html>
6. Conclusion
Using upload library to upload file to the server and insert records to the MySQL table while reading the file data.
You can also view this tutorial to know how to import CSV file data to the MySQL database in the CodeIgniter 4 project.
If you found this tutorial helpful then don't forget to share.
