In this php tutorial I will demonstrate to upload the CSV file to database using PHP and MySQL.

The following code should help beginner PHP/MySQL developers who are looking for an easy way to import a CSV or comma delimited file into a mysql database. This example adds the first name and last name into the table from an uploaded CSV file populating the following three fields: (firstName, lastName).

STEP 1: Create a Database in MySQL

The first step in this tutorial is the creation of a MySQL database. Create a database named csv using the following SQL query.

CREATE DATABASE csv;

STEP 2: Create a Table in MySQL.

The second step in this tutorial is the creation of a MySQL table. Create a table csv in database using the following SQL query.

CREATE TABLE IF NOT EXISTS csv ( 
id int(11) NOT NULL AUTO_INCREMENT , 
firstName varchar(200) NOT NULL , 
lastName varchar(200) NOT NULL , 
PRIMARY KEY (id) 
)

STEP 3: Create a MySQL Connection.

Create a separate file dbconnect.php to handle the database connection.Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:

<?php
$host = 'localhost';
$user = 'Your localhost Username';
$password = 'Your localhost password';
$db = 'Your Database name';
$con = mysqli_connect( $host , $user, $password ) or die ( 'Could not connect to server' . mysqli_error ($con) );
mysqli_select_db( $con , $db ) or die ( 'Could not connect to database' . mysqli_error ($con) );
?>

So now your dbconnect.php look like above.

STEP 4: Create a HTML Code to select CSV file.

After the successful connection to the database has been made, we will create the HTML file that could upload CSV file.

Create a new file and name it as index.php. This is a simple form for uploading CSV file. When the user submits the form, all records will be saved in the database.

<form role="form" action="index.php" method="post" enctype="multipart/form-data">
    Select image to upload:
    <input type="file" name="file" id="file">
    <input type="submit" value="Upload" name="submit">
</form>

STEP 5: Create a PHP Code to Save CSV Data to Database

You might notice that I have set an action as index.php in the form of the index.php because I will be writing my php code in the same pageindex.phpwhere I have written the HTML code. If you want to write php code in different page than you can set action with file name and write the php code in that file.

if(isset($_POST['submit'])){
    require 'dbconnect.php';

    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file,"r" );
    $c = 0 ; // define row count flag

    while (($csvData = fgetcsv($handle,1000,",")) !== false ){
        $firstName = $csvData[0];
        $lastName = $csvData[1];
        $sql = "INSERT INTO csv(firstName,lastName) VALUES ('$firstName', '$lastName')";
        $query = mysqli_query ($con, $sql);
        $c = $c + 1 ;
    }
    if ($query){
        echo "Csv data uploaded Sucessfully.";
    }else{
        echo "Error Occured";
    }
}

Now, your final index.php will look like

<?php

if(isset($_POST['submit'])){
    require 'dbconnect.php';

    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file,"r" );
    $c = 0 ; // define row count flag

    while (($csvData = fgetcsv($handle,1000,",")) !== false ){
        $firstName = $csvData[0];
        $lastName = $csvData[1];
        $sql = "INSERT INTO csv(firstName,lastName) VALUES ('$firstName', '$lastName')";
        $query = mysqli_query ($con, $sql);
        $c = $c + 1 ;
    }
    if ($query){
        echo "Csv data uploaded Sucessfully.";
    }else{
        echo "Error Occured";
    }
}
?>
<!DOCTYPE html>
<html>
<body>

<form role="form" action="index.php" method="post" enctype="multipart/form-data">
    Select image to upload:
    <input type="file" name="file" id="file">
    <input type="submit" value="Upload" name="submit">
</form>

</body>
</html>

Conclusion

In this article, I discussed how you could import data from CSV files using PHP and MySQL. This is a simple example you can add more complex logic and validations as per your requirements. If you wish to add to the discussion or would like to ask a question, leave a comment below.

Download Code from Below

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *