Upload the CSV file content to MySQL Database and List the content as table Using Codeigniter

Codeigniter

This article covers how to upload csv file content to database using php codeigniter framework without the help of any external libraries. We already discuss excel file content upload to MySQL database using codeigniter. Here we can upload only csv file contents, and insert each row content into database table row and finally lists all database table rows.

For uploading csv file and list all database records in tabular column uses jquery ajax bootstrap with codeingiter framework. First you create a new database ‘demo’ and a table ‘worker’ to upload the csv file. Use pathinfo() function to get the extension of  uploaded file and allow to upload csv file only.  Then open the csv file in php by using fopen() function and convert the csv content into php array with the help of php in-built function fgetcsv(). So then the array contains all uploaded information of each worker and using a loop insert each worker info to table row. Please go through the steps for detailed description.

 

step 1: create database and table

Create a database called demo in your MySQL . Then copy below code and paste on your sql section to create a table called ‘worker’.

CREATE TABLE `worker` (
 `WORKER_ID` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `FIRST_NAME` varchar(20) NOT NULL,
 `LAST_NAME` varchar(20) NOT NULL,
 `SALARY` int(5) NOT NULL ,
 `JOINING_DATE` datetime NOT NULL ,
 `DESIGNATION` varchar(20) NOT NULL ,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2: Install codeigniter framework on your web access folder

Step 3: Database connection

Open your database.php file from application/config/ of your codeigniter application and update hostname,database name,username and password to access your MySQL(default username is root and password is empty).

'hostname' => '********',//your host address (using xaampp or wamp hostname is localhost)
 'username' => 'root',
 'password' => '', 
'database' => 'demos',

Step 4: Create a controller

Create a new controller Csv.php under application/controllers/ of your root directory.

<?php defined('BASEPATH') OR exit('No direct script access allowed');
 class Csv extends CI_Controller {
        function __construct() { 
             parent::__construct(); 
             $this->load->model('CsvModel');
	}
	public function index(){

		$data['users'] = $this->CsvModel->fetch_users();
		$this->load->view('v_workers',$data);
	}
	public function upload()
	{
		$this->load->view('v_upload');
	}
	public function upload_file_content(){

		if(isset($_POST['upload'])){

			if(isset($_FILES['file']['name']) && $_FILES['file']['name'] != ''){

				$allowed =  array('csv');
				$filename = $_FILES['file']['name'];
				$ext = pathinfo($filename, PATHINFO_EXTENSION);
				if(!in_array($ext,$allowed) ) {
					$this->session->set_flashdata('msg_err','uploaded 
                                        file is not a csv file');
					redirect('csv/upload');
				}
				else{
					//upload
					$file_data = fopen($_FILES['file']['tmp_name'], 'r');
					$column = fgetcsv($file_data);
	 				while($row = fgetcsv($file_data))
				 	{
				  		$input = [
				  			'FIRST_NAME' => $row[0],
				  			'LAST_NAME' =>  $row[1],
				  			'SALARY' =>	$row[2],
				  			'JOINING_DATE' => $row[3],
				  			'DEPARTMENT'  => $row[4],
				  		];
				  		$this->CsvModel->insert($input);
				 	}
				 	// print_r($input);
			 		redirect('csv');
			 	}
			}
			else{
				$this->session->set_flashdata('msg_err','File not select');
				redirect('csv/upload');
			}
		}
	}
}

Load the model in the constructor function.

index() function fetch all workers from worker database and loads the view for listing all workers. Upload() loads the form for upload the csv file. upload_file_content() function hands the ajax request to upload csv file content to mysql database.

Step 5 : Autoload libraries and helpers

Autoload session and database libraries and url helper in autoload.php file of apploication/condig/.

$autoload['libraries'] = array('database','session');

$autoload['helper'] = array('url');

Step 6: create model

create a new model Csvmodel.php in application/models/ directory and save with below code. Fetch_users() function selects all data from table worker with ascending order, insert() function insert each worker info to table worker.

<?php class CsvModel extends CI_Model {
        public function fetch_users(){ 
                $this->db->select('*');
		$this->db->from('worker');
		$this->db->order_by('JOINING_DATE','ASC');
		return $this->db->get()->result_array();
	}
	public function insert($data){
		$this->db->insert('worker',$data);
		return true;
	}
}

 

Step 7: create the views

Here creats views for upload csv file (v_upload.php) and another for lists all worker details as table format(v_workers.php). These two view files are created on application/views/ directory.

v_upload.php

create this file on views and copy below code and paste into your v_upload.php file. This page contains a form to upload csv file.

<!DOCTYPE html>
   <html>
      <head> 
           <title>Upload CSV File</title> 
           <!-- Latest compiled and minified CSS --> 
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/
           bootstrap.min.css">
           <!-- jQuery library --> 
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></s
           cript>
           <!-- Popper JS --> 
           <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.
           min.js"></script>
           <!-- Latest compiled JavaScript --> 
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js">
           </script>
           <!-- Font Icons --> 
           <link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome
           .min.css" rel="stylesheet" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+
           QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
     </head>
     <body>
         <div class="container mt-5"> 
            <div>
                <h4>Upload the CSV file content to Database</h4>
            </div> 
            <form method="post" action="<?= site_url('csv/upload_file_content') ?>" enctype="
            multipart/form-data" >
                <div class="row"> 
                    <div class=" col-md-10 form-group"> 
                        <input type="file" name="file" class="form-control"> 
                           <span class="text-danger">
                              <?php echo $this->session->flashdata('msg_err'); ?>
                           </span> 
                    </div> 
                    <div class="col form-group"> 
                      <input type="submit" name="upload" value="Upload" class="btn btn-info">
                    </div> 
              </div> 
           </form> 
       </div>
    </body>
</html>

v_workers.php

create this file on views and copy below code and paste into your v_workers.php file. This page lists all worker information in table format.

<!DOCTYPE html>
<html>
     <head>
          <title>Upload CSV File</title> 
          <!-- Latest compiled and minified CSS --> 
          <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css
          /bootstrap.min.css">
          <!-- jQuery library --> 
          <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js">
          </script>
          <!-- Popper JS --> 
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper
          .min.js"></script>
          <!-- Latest compiled JavaScript --> 
          <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.
          js"></script>
     </head>
     <body>
          <div class="container mt-5"> 
               <div>
                    <h4>Upload the CSV file content to Database and List the content as 
                    table</h4>
               </div> 
               <div class="row m-3"> 
                    <a href="<?= site_url('csv/upload') ?>" class="btn btn-success">Upload
                    Csv</a> 
               </div> 
               <table class="table table-bordered"> 
                    <tr> 
                         <th>Name</th> 
                         <th>Salary</th> 
                         <th>Join date</th> 
                         <th>Desgination</th> 
                    </tr> 
                    <?php if(!empty($users)){ 
                          foreach($users as $index => $value){ 
                                echo '<tr><td>'.$value['FIRST_NAME'].' '.$value
                                ['LAST_NAME'].'</td>'; 
                                echo '<td>'.$value['SALARY'].'</td>';
                                echo '<td>'.$value['JOINING_DATE'].'</td>'; 
                                echo '<td>'.$value['DEPARTMENT'] .'</td></tr>'; 
                          }
                      }
                    ?> 
              </table> 
        </div>
      </body>
</html>

Step 8:open this application in your browser.

Lists all workers and a button to upload csv file content
csv file upload form

Leave a Reply

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