How to Upload CSV file into MySQL Database Using PHP

What Will I Learn?

You will learn how to upload/insert data from a csv file into the MySQL Database using MySQLi and PHP

Requirements

WAMP/MAMP/LAMP/XAMPP or any other PHP/MySQL web server solution stack package, text editor(Notepad ++, Sublime text, Brackets, Codeblock, PHP storm and any other text editor)

Difficulty

Intermediate

Tutorial Contents

CSV which stands for Comma Separated Values is a type of document that is created using Microsoft excel program. Most web application such as school portals make use of this CSV file format in terms of result/student information upload to the database because the manual process of upload can sometimes be very tedious. We are going to go through the steps to take when trying to upload a CSV file into a MySQL database using PHP.

STEP 1: Create an HTML page which contains an HTML form

<!DOCTYPE html>
<html>
       <head>
               <title>CSV Upload</title>
       </head>
       <body>
                <form method="POST" enctype="multipart/form-data" action="<?php echo $_SERVER["PHP_SELF"]; ?>">
                         <div align="center">
                                  <p>Select CSV file: <input type="file" name="file"  /></p>
                                  <p><input type="submit" name="csv_upload_btn" value="Upload"  /></p>
                         </div>
                </form>
       </body>
</html>

method="POST" This refers to how the form will be processed. When the method="POST" is used, important form contents like passwords will not be displayed in the browser url bar. Unlike the method="GET" which displays information in the browser url bar.
enctype="multipart/form-data" This is mostly used when ever a file is to be uploaded that is when the type="file" is used in an input tag <input type="file" />.
action refers to the page that will process the form. Using <?php echo $_SERVER["PHP_SELF"]; ?> means the form will be processed by the same page. Another way to acheive this is by just adding the name of the page action="pagename.php" or by leaving the action empty action="". I will advice that the first is used as it is known to be a good programming skill.
<div align="center"> refers to a division block tag that will contain all the form contents. The center property of the align attribute keeps all the content at the center of the page as displayed in a web browser.
<input type="file" name="file" /> This input tag is used where a file is to be selected. The name="file" will be used in the PHP part of this tutorial to process this particular input tag.
<input type="submit" name="csv_upload_btn" value="Upload" /> This is the submit button that must be clicked before the csv file will be uploaded. The type="submit" is an HTML form type that is used to create a button, the name="csv_upload_btn" will be used in the PHP part of this tutorial to process the button while the value="Upload" is what the button will display in the browser.

Note: The name attribute in any input tag or all tags related to forms are to be used in the PHP part of this series to process that particular tag with its specified name property.

I assume that you have an existing/intermediate knowledge on how to use HTML. Hence the rest of the tags used are familiar.

STEP 2: Using WAMP/MAMP/LAMP/XAMPP or any other PHP/MySQL web server solution stack package, Create a database.

For the sake of this tutorial, I will be using testCSV as my database name, localhost as my servername, root as my username and password as my password.

STEP 3: Create a database table using WAMP/MAMP/LAMP/XAMPP or any other PHP/MySQL web server solution stack package, Create a database.

In this tutorial we will be using users as our table name and the table will have two fields, a name field and an email field.

The name field is where the name of users will be stored while the email field is where user emails will stored in the database.

STEP 3: Process the form data and upload into database

<?php
//Create Connection
$connection = mysqli_connect("localhost", "root", "password", "testCSV");

// Check Connection
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

//Process form
if(isset($_POST["csv_upload_btn"])){
if($_FILES['file']['name']){
$filename = explode("",$_FILES['file']['name']);
if($filename[1] == "csv"){
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle)){
$item1 = mysqli_real_escape_string($connection, $data[0]);
$item2 = mysqli_real_escape_string($connection, $data[1]);

$query = " INSERT INTO users(name, email) VALUES('$item1', '$item2') ";
$run_query = mysqli_query($connection, $query);
}
fclose($handle);
if($run_query == true){
echo "File Import Successful";
}else{
echo "File Import Failed";
}
}
}
}

//Close Connection
mysqli_close($connection);
?>

Code explanation:

$connection = mysqli_connect("localhost", "root", "password", "testCSV"); This is a code for database connection. It connects you to your mysql database server using PHP script. localhost is the servername, root is the username of the mysql database, password is the password to the mysql database and testCSV is the database name.
if (!$connection) {die("Connection failed: " . mysqli_connect_error());} is used to check if the connection was successful and it will return an error message on the browser if the connection failed.

if(isset($_POST["csv_upload_btn"])) is to check when the button is clicked that is, the script can only be executed if only the button is clicked.

if($_FILES['file']['name']) is used to process the input field with name="file". This is where PHP processes the file in the HTML form.

$filename = explode("",$_FILES['file']['name']); this attaches . symbol to the file name. The explode function split strings by a specified string.

$filename = explode("",$_FILES['file']['name']);if($filename[1] == "csv") checks whether the selected file extension is csv. The script will only continue if the selected file is a .csv file.

$handle = fopen($_FILES['file']['tmp_name'], "r"); opens the selected file and reads its content.
while($data = fgetcsv($handle)){$item1 = mysqli_real_escape_string($connection, $data[0]);$item2 = mysqli_real_escape_string($connection, $data[1]);$query = " INSERT INTO users(name, email) VALUES('$item1', '$item2') ";$run_query = mysqli_query($connection, $query);} the while loop, loops through the content of the csv file to fetch/get its content and insert it into the database using the PHP insert query.
fclose($handle); closes the opened file that was selected.

if($run_query == true){echo "File Import Successful";}else{echo "File Import Failed";} this displays a successful message if the file was successfully uploaded and displays a failed message if the file failed to upload.
mysqli_close($connection); this closes the connection that created earlier.

Curriculum

This the first tutorial on this series, the next will be on how to export data from MySQL database into a CSV file using PHP.

Posted on Utopian.io - Rewarding Open Source Contributors