Load CSV Data into MySQL Server Using PHP

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>