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

Hey @casweeney I am @utopian-io. I have just upvoted you!

Achievements

You have less than 500 followers. Just gave you a gift to help you succeed!
Seems like you contribute quite often. AMAZING!

Suggestions

Contribute more often to get higher and higher rewards. I wish to see you often!
Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

Vote for my Witness With SteemConnect
Proxy vote to Utopian Witness with SteemConnect
Or vote/proxy on Steemit Witnesses

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

Sort Order:

Trending

[-]

howo (70) · 7 years ago

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

$1.63

2 votes

utopian.tip (46) · 7 years ago

Hey @howo, I just gave you a tip for your hard work on moderation. Upvote this comment to support the utopian moderators and increase your future rewards!

$0.00

casweeney (57) · 7 years ago

Thank you very much @howo for approval. I am grateful.

biyanoor (50) · 7 years ago (edited)

Nice article.. You are doing extraordinary well.. I cant understand it as i am not computer student but one thing is clear that you describe evverything in detail..
And thanx for resteeming..

1 vote

Hahaha, its alright you are welcome. That's the least we do for ourselves.

biyanoor (50) · 7 years ago

Right.. God bless you

javapoint (54) · 7 years ago

i seriously dont know how you manage to explain all of this in details.thumbs up and keep the good work going.
BR
JAVAPOINT

Sure boss. Its what we do and have been doing for long. Those that writes the same language, understands it very well. Its just as easy as you speak English and your native language.

Thanks for stopping by @javapoint.

forsterobiora (33) · 7 years ago

Nice one bro

Thank you sir.

utopian-io (71) · 7 years ago