In the previous tutorial we created the How to export mysql to csv using php, and now as continue of this tutorial we create also on how to import csv to mysql.
A table contains a listed of data and upload form, user can be able to upload the file, and also we create a php function for the file upload handling, validation and etc.
You can view the live demo and test the upload function, the test cvs file are available in the previous tutorial How to export mysql to csv using php.
So now let’s begin the export function.
Demo
Step 1: Create a Database
First create a database named ‘import_csv’ you can change if you want, and create a table named ‘csv_data’ and fields, and insert some data.
CREATE TABLE `csv_data` ( `id` int(10) UNSIGNED NOT NULL, `product_title` varchar(255) NOT NULL, `product_price` varchar(255) NOT NULL ); ALTER TABLE `csv_data`ADD PRIMARY KEY (`id`); ALTER TABLE `csv_data` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Step 2: Create Database Connection
Create file name ‘database.php’ to connect from the mysql database we have assigning the database credentials, MySQL host, user, password and database name the ‘import_csv’.
<?php
$db = mysqli_connect('localhost', 'root', '', 'axlmulat_demo');
if(!$db) { echo mysqli_connect_error(); }
?>
Step 3: Creating the Page Template
Create a file name ‘index.php’ and copy the code below. In the index page, first we connect to mysql database connection and query the added sample product in the table, and upload form set the attribute enctype multipart for upload handling.
index.php
<?php require_once("database.php"); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="robots" content="noindex,nofollow"/>
<title>Importing Data with CSV</title>
<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
</head>
<body>
<div id="wrap" align="center"> <!--wrap start-->
<h1>Importing Data with CSV</h1>
<table id="table_data">
<tr>
<td>id</td>
<td>Product name</td>
<td>Price</td>
</tr>
<?php
$query = mysqli_query($db, "SELECT `id`, `product_title`, `product_price` FROM `csv_data`");
while($row = mysqli_fetch_assoc($query)) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['product_title']; ?></td>
<td><?php echo $row['product_price']; ?></td>
</tr>
<?php } unset($row); ?>
</table>
<form action="import.php" method="post" enctype="multipart/form-data">
<input name="cvs" type="file" />
<input name="upload" type="submit" value="Upload CSV"/>
</form>
<?php
if(isset($_GET['success'])) {
echo '<span class="msg">' . $_GET['count'] . ' Data Successfully Imported.' . '</span>';
}
?>
</div> <!--wrap end-->
</body>
</html>
A simple style for the page.
style/style.css
body {
font-family: Arial, Helvetica, sans-serif;
font-size:13px;
color:#222222;
}
h1 {
color:#000000;
font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
}
p {
margin:10px;
padding:10px;
color:#000000;
}
table#table_data {
width: 325px;
}
table#table_data tr:first-child {
font-weight:bold;
text-transform:uppercase
}
table#table_data tr td {
border-bottom: 1px solid #CCCCCC;
text-align:center
}
table#table_data tr:first-child {
text-align:center;
border:1px solid #999
}
span.msg {
background: url(correct.png) no-repeat right;
display: block;
font-style: italic;
margin-top: 10px;
width: 210px;
}
Step 4: The Import PHP Script
import.php
<?php require_once("database.php");
if(@$_POST['upload']) {
$file_name = $_FILES['cvs']['name'];
$file_type = $_FILES['cvs']['type'];
$file_temp_loc = $_FILES['cvs']['tmp_name'];
$file_error_msg = $_FILES['cvs']['error'];
$file_size = $_FILES['cvs']['size']; /* 1. file upload handling */
if(!$file_temp_loc) { // if not file selected
echo "Error: please browse for a file before clicking the upload button.";
exit();
}
if(!preg_match("/\.(csv)$/i", $file_name)) { // check file extension
echo 'Error: your file is not CSV.';
@unlink($file_temp_loc); // remove to the temp folder
exit();
}
if($file_size > 5242880) { // file check size
echo "Error: you file was larger than 5 Megabytes in size.";
exit();
}
if($file_error_msg == 1) { //
echo "Error: an error occured while processing the file, try agian.";
exit();
}
$move_file = move_uploaded_file($file_temp_loc, "upload/{$file_name}"); // temp loc, file name
if($move_file != true) { // if not move to the temp location
echo 'Error: File not uploaded, try again.';
@unlink($file_temp_loc); // remove to the temp folder
exit();
}
$csvFile = 'upload/'.$file_name;
$csvFileLength = filesize($csvFile);
$csvSeparator = ",";
$handle = fopen($csvFile, 'r');
$count = '';
while($data = fgetcsv($handle, $csvFileLength, $csvSeparator)) { // while for each row
$count += count($data[0]); // count imported
mysqli_query($db, "INSERT INTO `csv_data` (`product_title`, `product_price`) VALUES ( '$data[0]', '$data[1]' )");
}
fclose($handle);
unlink($csvFile); // delete cvs after imported
header('Location: index.php?success=1&count='.$count);
exit();
}
?>
In the php import script we connect to mysql database first. If the user upload the file, the $_FILES assigns the variables, and we put in the upload validation. After the validation we put in the move uploaded file function to transferred the csv to the server, we will use the fopen function for open the file and get contents and then perform a while loop with mysql queries, after performing the query we put in the unlink to delete the cvs file in server for saving space.
Step 5: Complete
Were done, we created a php web application can be able to import csv to MySQL. Let’s have a look at what we’ve achieved:
- We’ve set up a database.
- We’ve create page to fetch the data.
- We’ve create a file upload handling with validation.
- We’ve able to count the uploaded data.
- We’ve able to delete the cvs after imported.
If you enjoyed this article, please consider sharing it!
