So let’s start the coding. Before begin, take a look at files structure used for this tutorial.
- index.php
- db_connect.php
- import.php
Step1: Create Database Table
In this tutorial we will import employee records into MySQL Database. So first we will create MySQL database table emp to store employee records.
CREATE TABLE IF NOT EXISTS `emp` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`emp_name` varchar(255) NOT NULL COMMENT 'employee name',
`emp_email` varchar(100) NOT NULL,
`emp_salary` double NOT NULL COMMENT 'employee salary',
`emp_age` int(11) NOT NULL COMMENT 'employee age',
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Step2: Create Database Connection
After creating MySQL table emp, we will create db_connect.php file to make connection with MySQL database.
<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>
Steps3: Include Bootstrap and jQuery Files
As in this tutorial we have created HTML using Bootstrap, so we include Bootstrap files and also jQuery in head tag in index.php.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
Steps4: Create CSV File Upload Form and Display Records
Now in index.php, we will create a FORM to upload employee CSV file and also create HTML to display imported employee records.
<div class="container">
<div class="panel panel-default">
<div class="panel-body">
<br>
<div class="row">
<form action="import.php" method="post" enctype="multipart/form-data" id="import_form">
<div class="col-md-3">
<input type="file" name="file" />
</div>
<div class="col-md-5">
<input type="submit" class="btn btn-primary" name="import_data" value="IMPORT">
</div>
</form>
</div>
<br>
<div class="row">
<table class="table table-bordered">
<thead>
<tr>
<th>Emp Id</th>
<th>Emp Name</th>
<th>Emp Email</th>
<th>Emp Age</th>
<th>Salary ($)</th>
</tr>
</thead>
<tbody>
<?php
$sql = "SELECT emp_id, emp_name, emp_email, emp_salary, emp_age FROM emp ORDER BY emp_id DESC LIMIT 10";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
if(mysqli_num_rows($resultset)) {
while( $rows = mysqli_fetch_assoc($resultset) ) {
?>
<tr>
<td><?php echo $rows['emp_id']; ?></td>
<td><?php echo $rows['emp_name']; ?></td>
<td><?php echo $rows['emp_email']; ?></td>
<td><?php echo $rows['emp_salary']; ?></td>
<td><?php echo $rows['emp_age']; ?></td>
</tr>
<?php } } else { ?>
<tr><td colspan="5">No records to display.....</td></tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
Steps5: Handle CSV File Import
Now we will handle functionality to import employee CSV file data into MySQL Database in import.php. We will check for valid CSV file and then read file and insert/update employee records in emp MySQL database table.
<?php
include_once("../db_connect.php");
if(isset($_POST['import_data'])){
// validate to check uploaded file is a valid csv file
$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$file_mimes)){
if(is_uploaded_file($_FILES['file']['tmp_name'])){
$csv_file = fopen($_FILES['file']['tmp_name'], 'r');
//fgetcsv($csv_file);
// get data records from csv file
while(($emp_record = fgetcsv($csv_file)) !== FALSE){
// Check if employee already exists with same email
$sql_query = "SELECT emp_id, emp_name, emp_salary, emp_age FROM emp WHERE emp_email = '".$emp_record[2]."'";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
// if employee already exist then update details otherwise insert new record
if(mysqli_num_rows($resultset)) {
$sql_update = "UPDATE emp set emp_name='".$emp_record[1]."', emp_salary='".$emp_record[3]."', emp_age='".$emp_record[4]."' WHERE emp_email = '".$emp_record[2]."'";
mysqli_query($conn, $sql_update) or die("database error:". mysqli_error($conn));
} else{
$mysql_insert = "INSERT INTO emp (emp_name, emp_email, emp_salary, emp_age )VALUES('".$emp_record[1]."', '".$emp_record[2]."', '".$emp_record[3]."', '".$emp_record[4]."')";
mysqli_query($conn, $mysql_insert) or die("database error:". mysqli_error($conn));
}
}
fclose($csv_file);
$import_status = '?import_status=success';
} else {
$import_status = '?import_status=error';
}
} else {
$import_status = '?import_status=invalid_file';
}
}
header("Location: index.php".$import_status);
?>
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]