Import CSV file data into MySQL database using PHP CodeIgniter 4 example; In this tutorial, i will show you how to import data from csv or excel file into MySql database PHP codeigniter 4 app. And as well learn how insert bulk data into mysql database using PHP codeigniter 4 app.
If you have any csv and excel file and there is a lot of data in it. And you want to insert that data into MySQL database. So in this tutorial you will get a step by step guide with the help of which you can insert data from csv and excel files into database in your codeigniter 4 app.
How to import excel file data into mysql database in php codeigniter 4 tutorial; In this example, I will create simple form and controller, where form is used to send data to controller in PHP codeigniter 4 app and controller method is used to read csv or excel file & store data into mysql database.
How to Import Data from Excel Or CSV to MySQL Database in PHP Codeigniter 4
- Install Codeigniter 4 Application
- Basic App Configurations
- Create Database and Table
- Connect App to Database
- Create Model Class
- Create Controller Class
- Create View
- Setup Routes
- Start Development server
Step 1 – Install Codeigniter 4 Application
First of all, you need to ownload the latest version of Codeigniter 4. So, visit this link https://codeigniter.com/download Download Codeigniter 4 app and unzip the setup in your local system xampp/htdocs/ .
Note that, please change the download folder name “demo”.
Step 2 – Basic App Configurations
Now, you need to some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.
Set Base URL like this
public $baseURL = 'http://localhost:8080'; To public $baseURL = 'http://localhost/demo/';
Step 3 – Create Database and Table
Create a database and table by executing the following SQL query:
CREATE DATABASE demo; CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `city` varchar(255) NOT NULL, `status` varchar(255) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Step 4 – Connect App to Database
To connect your codeigniter 4 app to the database. So, visit app/Config/ directory and open Database.php. Then add the databasae details like below into database.php file:
public $default = [ 'DSN' => '', 'hostname' => 'localhost', 'username' => 'test', 'password' => '4Mu99BhzK8dr4vF1', 'database' => 'demo', 'DBDriver' => 'MySQLi', 'DBPrefix' => '', 'pConnect' => false, 'DBDebug' => (ENVIRONMENT !== 'development'), 'cacheOn' => false, 'cacheDir' => '', 'charset' => 'utf8', 'DBCollat' => 'utf8_general_ci', 'swapPre' => '', 'encrypt' => false, 'compress' => false, 'strictOn' => false, 'failover' => [], 'port' => 3306, ];
Step 5 – Create Model Class
Create Users.php file. So, visit app/Models directory and create Model.php.Then add the following code into it:
Step 6 – Create Controller Class
Create ImportDataController.php file. So, visit app/Controllers directory and create ImportDataController.php.Then add the following code into it:
<?php namespace App\Controllers; use CodeIgniter\Controller; use CodeIgniter\HTTP\RequestInterface; use App\Models\Users; class ImportDataController extends Controller { public function index() { return view('import'); } // File upload and Insert records public function importFile(){ // Validation $input = $this->validate([ 'file' => 'uploaded[file]|max_size[file,1024]|ext_in[file,csv],' ]); if (!$input) { // Not valid $data['validation'] = $this->validator; return view('users/index',$data); }else{ // Valid if($file = $this->request->getFile('file')) { if ($file->isValid() && ! $file->hasMoved()) { // Get random file name $newName = $file->getRandomName(); // Store file in public/csvfile/ folder $file->move('../public/csvfile', $newName); // Reading file $file = fopen("../public/csvfile/".$newName,"r"); $i = 0; $numberOfFields = 4; // Total number of fields $importData_arr = array(); // Initialize $importData_arr Array while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) { $num = count($filedata); // Skip first row & check number of fields if($i > 0 && $num == $numberOfFields){ // Key names are the insert table field names - name, email, city, and status $importData_arr[$i]['name'] = $filedata[0]; $importData_arr[$i]['email'] = $filedata[1]; $importData_arr[$i]['city'] = $filedata[2]; $importData_arr[$i]['status'] = $filedata[3]; } $i++; } fclose($file); // Insert data $count = 0; foreach($importData_arr as $userdata){ $users = new Users(); // Check record $checkrecord = $users->where('email',$userdata['email'])->countAllResults(); if($checkrecord == 0){ ## Insert Record if($users->insert($userdata)){ $count++; } } } // Set Session session()->setFlashdata('message', $count.' Record inserted successfully!'); session()->setFlashdata('alert-class', 'alert-success'); }else{ // Set Session session()->setFlashdata('message', 'File not imported.'); session()->setFlashdata('alert-class', 'alert-danger'); } }else{ // Set Session session()->setFlashdata('message', 'File not imported.'); session()->setFlashdata('alert-class', 'alert-danger'); } } return redirect()->route('/'); } }
Step 7 – Create View
Create home.php file. So, visit app/views directory and create home.php.Then add the following code into it:
<!DOCTYPE html> <html> <head> <title>Codeigniter 4 Import Excel or CSV File into Database Example - Laratutorials.com</title> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" /> <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script> </head> <body> <form action="<?php echo base_url();?>import-file" method="post" enctype="multipart/form-data"> Upload excel file : <input type="file" name="uploadFile" value="" /><br><br> <input type="submit" name="submit" value="Upload" /> </form> </body> </html>
Step 7 – Setup Routes
To define a route, So, visit app/Config/ directory and open Routes.php file. Then add the following routes into it:
$routes->setDefaultController('ImportDataController'); $routes->get('/', 'ImportDataController::index'); $routes->post('import-file', 'ImportDataController::importFile');
Step 8 – Start Development server
Execute the following command into command prompt or terminal to start the codeigniter 4 application:
php spark serve
Then visit your web browser and hit the following url on it:
http://localhost/demo/ OR http://localhost:8080/
Conclusion
Export data to excel in codeigniter 4 using phpexcel example; In this tutorial, You have learned how to export data from database using phpexcel library in codeigniter 4 app.
Recommended CodeIgniter 4 Tutorial
- How to Install / Download Codeigniter 4 By Manual, Composer, Git
- How to Remove Public and Index.php From URL in Codeigniter 4
- Codeigniter 4 – Form Validation Example Tutorial
- How to add jQuery Validation on Form in Codeigniter 4 Example
- Codeigniter 4 Ajax Form Submit Validation Example
- Codeigniter 4 File Upload Validation Example
- Image Upload with Validation in Codeigniter 4
- Codeigniter 4 Image Upload Preview Using jQuery Example
- Codeigniter 4 Ajax Image Upload Preview Example
- How to Upload Multiple Images in Codeigniter 4
- Codeigniter 4 Multiple Image Upload with Preview
- Codeigniter 4 Pagination Example; Create Pagination in Codeigniter
- Simple Codeigniter 4 CRUD with Bootstrap and MySQL Example
- Codeigniter 4 CRUD with Datatables Example
- Codeigniter 4 Image Crop and Save using Croppie Example
- Codeigniter 4 Dependent Dropdown using jQuery Ajax Example
- CodeIgniter 4 Rest Api CRUD Example
- Codeigniter 4 Login Registration and Logout Example
- Codeigniter 4 – Get Address from Latitude and Longitude Ex
- Codeigniter 4 Google Column Charts Example
- Google Pie Chart in Codeigniter 4
- Codeigniter 4 Ajax Load More Data on Page Scroll
- Codeigniter 4 Google ReCaptcha V2 Example
- Codeigniter 4 Export Data to Excel Example