Technical Tube

Learn PHP,HTML,CSS,Javascript,MySQL,Jquery

Saturday, 29 February 2020

Home Core PHP How To Import CSV FIle Data Into MySQL Using PHP

How To Import CSV FIle Data Into MySQL Using PHP

by on in

Import CSV Data Into MySQL Using PHP

In this tutorial, I will write about how to import csv file data into mysql database using php. I will send csv file using html input type file to perform php code in the file. There are many different ways to import csv file like fgetcsv(), php excel library. Here I am using fgetcsv to get coma separated values.

Note: Mysql database table columns and csv file columns must be equal for php import csv.

HTML Form to Import CSV into MySQL

First step is to create an html form and write an input tag to upload csv file. Then write a button for submitting html form to php read csv and php import csv into mysql database table. When a user click on submit button php code will read the file and mysql insert query will insert the csv data into mysql table using a while loop.  I am using bulma css for the designing of this html form.

html form import csv in php
html form for php import csv
<html> <head> <title> Import CSV File Using PHP </title> <script src="&lt;?php echo $base_url; ?&gt;/scripts/jquery-3.4.1.min.js"></script> <link href="&lt;?php echo $base_url; ?&gt;/style/bulma-0.7.4/css/bulma.min.css" rel="stylesheet" type="text/css"></link> <link href="&lt;?php echo $base_url; ?&gt;/style/bulma-0.7.4/css/bulma.css" rel="stylesheet" type="text/css"></link> </head> <body> <section class="hero is-fullheight is-success"> <div class="container"> <div class="box"> <h1 class="title has-text-centered" style="color: black;"> Import CSV File Using PHP</h1> <form action="" enctype="multipart/form-data" method="post" name="uploadCSV"> <div class="column is-6 is-offset-3"> <label class="label">Choose CSV File</label> <input accept=".csv" id="file" name="file" type="file" /> <button class="button is-info" id="btn_import" name="btn_import" type="submit">Import</button> </div> <div class="label"> </div> </form> </div> </div> </section> </body> </html>

Create Table in MySQL Database

Next I will create a table in mysql database to save imported data.Below is its query to create table in mysql database.

CREATE TABLE `person` ( `id` int(11) NOT NULL, `firstname` varchar(100) NOT NULL, `lastname` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `address` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `person` ADD PRIMARY KEY (`id`); ALTER TABLE `person` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


PHP Code to Import CSV Data to MySQL

In php code first of all store the file name within a varibale. The use an if statement to check the csv file size is greater than zero. Now read csv file using fopen() function. Using a while loop use fgetcsv() function in the while loop to parse the csv file and then write mysql insert query to import csv data into mysql.

Below is the code of php import csv.
include "config.php"; $message=''; if(isset($_POST['btn_import'])) { $fileName=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"]&gt;0) { $file=fopen($fileName, "r"); while(($column =fgetcsv($file, 10000, ",")) !==FALSE) { $query = "INSERT INTO `person`(`id`, `firstname`, `lastname`, `email`, `address`) VALUES ('".$column[0]."','".$column[1]."','".$column[2]."','".$column[3]."','".$column[4]."')"; $result = mysqli_query($conn, $query); if (! empty($result)) { $message = "CSV Data Imported into the Database"; } else { $message = "Problem in Importing CSV Data"; } } } }

Config.php File Code

$host="localhost"; // Host name $username="root"; // Mysql username $password=""; // Mysql password $db_name="test"; // Database name $conn=mysqli_connect($host,$username,$password,$db_name) or die ("Failed to connect with DB"); $base_url ='http://localhost/tutorials';
Now if you will complete all above steps csv data will be imported into mysql database table. After importing csv data lets display the data in html table usig mysql select query which is used to fetch data from database table.

HTML Table to Fetch Imported Data From MySQL

In this table first of all write mysql select query and execute the query. Then check if mysql table has records to display in html table using if statement in php code. If mysql table rows are greater than zero then display data in html table. 

Write this code after the closing tag of html form.
<div class="column is-6 is-offset-3"> 0) { ?&gt; <br /> <table class="table is-bordered is-narrow is-striped"> <thead> <tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Email</th> <th>Address</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> </tbody> </table> </div>
Below is the screenshot of html table. This table is the output of import csv into mysql table in php.

import csv in php output
php import csv output


Click Here To Download Source Code

Related Posts:

By at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)