Hi, this time I have come up with another useful php script, which will show you How to Import CSV File Data into MySQL Database using PHP. CSV Files commonly known as comma separated values is one of the most preferred data storage methods. And as a developer often you might find you in situation to import those huge csv data into database system. Though sounding simple, it's a nightmare in disguise. Here's where php comes to the rescue. With few lines of code, you can insert several hundreds of records in flash.
PHP supports several data formats like csv, json etc. and provides dedicated functions to handle such data formats. Hence without any third party solutions, we can to parse and store csv records in database using core php alone.
- How to Convert CSV To JSON File using PHP
- How to Import JSON File into MySQL using PHP
- How to Export MySQL to JSON File using PHP
Create Sample MySQL Database
As an example, let's use our previous 'library' database for this tutorial. Just create a new database with name 'library' in mysql. Next you should create a table 'books' to store the details of the books in the library. For that run the below sql command.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `library`
--
-- Table structure for table `books`
--
CREATE TABLE IF NOT EXISTS `books` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`author` varchar(30) NOT NULL,
`isbn` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample CSV File
Below is the sample csv file containing the books data and we'll import this csv file to the 'books' table.
books.csv
Learning PHP MySQL & JavaScript,Robin Nixon,ISBN-13: 978-1491918661
PHP and MySQL for Dynamic Web Sites,Larry Ullman,ISBN-13: 978-0321784070
PHP Cookbook,David Sklar,ISBN-13: 978-1449363758
Programming PHP,Kevin Tatroe,ISBN-13: 978-1449392772
Modern PHP New Features and Good Practices,Josh Lockhart,ISBN-13: 978-1491905012
Now it's time to move on to the php script part.
PHP Script to Import CSV File Data into MySQL Database
The CSV import process goes like this. With PHP open the '.csv' file, parse through it one line at a time and insert into mysql db. Following is the step by step procedure for doing so.
Step-1: Set the Database Connection Variables
First let's set the database connection variables and the right csv file path to import.
<?php
//set the connection variables
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "library";
$filename = "books.csv";
?>
Step-2: Establish the MySQL Database Connection
Next we open the connection to mysql database using mysqli library.
<?php
//connect to mysql database
$connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection));
?>
Step-3: Open and Parse the CSV File
Now we should open the csv file in 'reading' mode and parse the record one at a time and insert into mysql table 'books'.
<?php
// open the csv file
$fp = fopen($filename,"r");
//parse the csv file row by row
while(($row = fgetcsv($fp,"500",",")) != FALSE)
{
//insert csv data into mysql table
$sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')";
if(!mysqli_query($connection, $sql))
{
die('Error : ' . mysqli_error());
}
}
fclose($fp);
?>
The line $fp = fopen($filename,"r"); will open the 'books.csv' file for reading and return the file handler to the $fp variable.
Next we loop through the entire file one record at a time. Using the line $row = fgetcsv($fp,"500",",") will fetch the complete row of the csv file one at a time, and "500" specifies the maximum length of the rows in the csv file and "," specifies the delimiter.
Finally we implode the fetched row and save it into mysql database using the INSERT query.
The line fclose($fp); closes the file handler after completely parsing through the entire file.
Step-4: Close the MySQL Database Connection
After inserting all the csv records into mysql, we finally close the database connection we established first.
<?php
//close the db connection
mysqli_close($connection);
?>
Here is the Complete PHP Script to Import CSV File into MySQL Database.
<?php
//set the connection variables
$hostname = "localhost";
$username = "username";
$password = "password";
$database = "library";
$filename = "books.csv";
//connect to mysql database
$connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection));
// open the csv file
$fp = fopen($filename,"r");
//parse the csv file row by row
while(($row = fgetcsv($fp,"500",",")) != FALSE)
{
//insert csv data into mysql table
$sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')";
if(!mysqli_query($connection, $sql))
{
die('Error : ' . mysqli_error());
}
}
fclose($fp);
//close the db connection
mysqli_close($connection);
?>
Now go back to mysql and check the library database after running the php snippet. You will find the csv file records inserted into the 'books' table.
And that was all about importing csv file into mysql database using php. There's also the reverse process of exporting mysql database to csv file with php. You can check it out.
