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.
