Load CSV Data into MySQL Server Using PHP
Last Updated :
14 Dec, 2020
In this article, we are going to store data present in CSV file into MySQL database using PHP in XAMPP server and display the data on web browser.
Comma Separated Value (CSV) is a text file containing data contents. It facilitates the storage of data in a table like structure. CSV files are stored with a CSV extension. A CSV file can be created with the use of any text editor such as notepad, notepad++, etc. After adding content to a text file in the notepad, store it as a csv file with the use of .csv extension.
Requirements: XAMPP Server
Steps:
- Create connection to database
- Load CSV
- Execute Query and verify results
Syntax with Step by Step:
1. Create connection to database
<?php
$db=new mysqli('servername','username','password','databasename');
if ($db->connect_errno) {
echo "Failed " . $db->connect_error;
exit();
}
?>
Here, Servername is the localhost, username is your user default is root, password is empty by default. database name is name of your database.
2. Take CSV file using fopen function
It is used to open a file
fopen(filename, mode, include_path, context)
Here:
filename is used to specify the file or URL to open
mode is to Specify the type of access you require to the file/stream.
3. Get the CSV file: We can get CSV file using fgetcsv() function
4. Database Query
Now we can write database query to insert data
$db->query('INSERT INTO table VALUES ("' . $row[0] . '", "' . $row[1] . '", "' . $row[2] . '" . . . "' . $row[n] . '")');
Query is used to take query
row[n] represents the number of rows to be taken to load
Process to Create Database and table
1. Consider the CSV file named detail.csv
2. Open XAMPP and start MySQL, Apache service
3. Type "http://localhost/phpmyadmin/" in your browser
4. Click on new and create database named "gfg"
5. Create table with name "table2"
Execution Steps:
1. Store detail.csv and index.php code files in one folder under the path "C:\xampp\htdocs\gfg"
2. Open index.php file and type the code given below:
PHP
<?php
// Create connection
// Localhost is the server name,
// root is the username,
// password is empty
// database name is gfg
$db = new mysqli('localhost', 'root', '', 'gfg');
// Checking connection
if ($db->connect_errno) {
echo "Failed " . $db->connect_error;
exit();
}
?>
<h1>
html table code for displaying
details like name, rollno, city
in tabular format and store in
database
</h1>
<table align="center" width="800"
border="1" style=
"border-collapse: collapse;
border:1px solid #ddd;"
cellpadding="5"
cellspacing="0">
<thead>
<tr bgcolor="#FFCC00">
<th>
<center>NAME</center>
</th>
<th>
<center>ROLL NO</center>
</th>
<th>
<center>CITY</center>
</th>
</tr>
</thead>
<tbody>
<?php
// Get csv file
if(($handle = fopen("detail.csv",
"r")) !== FALSE) {
$n = 1;
while(($row = fgetcsv($handle))
!== FALSE) {
// SQL query to store data in
// database our table name is
// table2
$db->query('INSERT INTO table2
VALUES ("'.$row[0].'","'.$row[1].'",
"'.$row[2].'")');
// row[0] = name
// row[1] = rollno
// row[2] = city
if($n>1) {
?>
<tr>
<td>
<center>
<?php echo $row[0];?>
</center>
</td>
<td>
<center>
<?php echo $row[1];?>
</center>
</td>
<td>
<center>
<?php echo $row[2];?>
</center>
</td>
</tr>
<?php
}
// Increment records
$n++;
}
// Closing the file
fclose($handle);
}
?>
</tbody>
</table>
Output:
Output in web page
Go to localhost/phpmyadmin and refresh gfg data base to view the stored data.
Similar Reads
How to make a connection with MySQL server using PHP ?
MySQL is a widely used database management system that may be used to power a wide range of projects. One of its main selling features is its capacity to manage large amounts of data without breaking a sweat. There are two approaches that can be used to connect MySQL and PHP code, which are mentione
3 min read
How to retrieve data from MySQL database using PHP ?
There are steps to understand for retrieving the data from the MySQL database. Approach: Create the database, then create the table for data.Enter the rows in the table. You have to connect to the database. Now we understand each and every step as shown below.  Example 1: In this. we use PHPMyAdmin
2 min read
How to Insert JSON data into MySQL database using PHP?
To insert JSON data into MySQL database using PHP, use the json_decode function in PHP to convert JSON object into an array that can be inserted into the database. Here, we are going to see how to insert JSON data into MySQL database using PHP through the XAMPP server in a step-by-step way. JSON Str
3 min read
PHP | mysqli_real_escape_string() Function
The mysqli_real_escape_string() function is an inbuilt function in PHP which is used to escape all special characters for use in an SQL query. It is used before inserting a string in a database, as it removes any special characters that may interfere with the query operations. When simple strings ar
2 min read
How to Convert XML data into JSON using PHP ?
In this article, we are going to see how to convert XML data into JSON format using PHP. Requirements: XAMPP Server Introduction: PHP stands for hypertext preprocessor, which is used to create dynamic web pages. It also parses the XML and JSON data. XML stands for an extensible markup language in wh
3 min read
How to Update Data in MySQL Database Table Using PHP?
Updating data in a MySQL database table using PHP is a fundamental aspect of web development, particularly in applications where user interactions involve modifying existing records. This guide delves into the process of updating data in a MySQL database table using PHP, covering database connection
3 min read
Create a Small CRM using PHP and MySQL
CRM stands for Customer Relationship Management, which is a strategy for a set of practices, and a technology designed to manage and analyze customer interactions and data throughout the customer lifecycle. Manage contacts by adding, updating, and deleting information such as name, email, phone, and
6 min read
CRUD Operation in MySQL Using PHP, Volley Android - Read Data
In the previous article, we have performed the insert data operation. In this article, we will perform the Read data operation. Before performing this operation first of all we have to create a new PHP script for reading data from SQL Database. Prerequisite: You should be having Postman installed i
9 min read
Build a Grocery Store Web App using PHP with MySQL
In this article, we are going to build a Grocery Store Web Application using PHP with MySQL. In this application, we can add grocery items by their name, quantity, status (pending, bought, not available), and date. We can view, delete and update those items. There will be a date filtering feature wh
7 min read
PHP | MySQL Database Introduction
What is MySQL? MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.MySQL
4 min read