If user want to excel file data store in mysql database at one time,you can follow the following demo then you can achieve the target.
for this tutorial we are working with simple CSV file and this file data insert into database. large or huge amount of data you can insert into database sometimes user can upload bulk data into database If we insert data one by one it will be very difficult and time consuming. Here You will learn how to insert bulk of data in database from excel sheet in a couple of minutes.
Demo link :
http://freeteachnology.hol.es/ExcelMysql/index.php
Code link :
https://drive.google.com/open?id=0BxmTZPVcu72fWHVLNF9odVZMWmc
for this tutorial we are working with simple CSV file and this file data insert into database. large or huge amount of data you can insert into database sometimes user can upload bulk data into database If we insert data one by one it will be very difficult and time consuming. Here You will learn how to insert bulk of data in database from excel sheet in a couple of minutes.
Demo link :
http://freeteachnology.hol.es/ExcelMysql/index.php
Code link :
https://drive.google.com/open?id=0BxmTZPVcu72fWHVLNF9odVZMWmc
my code script run only .csv file not other excel csv file please keep in mind and remember that this point.
We can upload excel sheet data to mysql database table using php that is very simple. For that you have to follow the following steps.
If you follow the below steps end of the this blog you achieve the target.
1. First you have to create mysql database.
EX:create database excel
2. Create table in your chosen database.
create database query is following please make copy and past into mysql database :
Crate Table name : ExcelMysql
Code :
-- phpMyAdmin SQL Dump -- version 4.5.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 06, 2017 at 08:36 AM -- Server version: 10.1.13-MariaDB -- PHP Version: 5.6.20 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `excel` -- -- -------------------------------------------------------- -- -- Table structure for table `ExcelMysql` -- CREATE TABLE `ExcelMysql` ( `id` int(11) NOT NULL COMMENT 'primary id', `fname` varchar(256) DEFAULT NULL COMMENT 'first name', `lname` varchar(256) DEFAULT NULL COMMENT 'last name', `mobile` int(101) DEFAULT NULL COMMENT 'mobile no', `email` varchar(256) DEFAULT NULL COMMENT 'email id', `address` text COMMENT 'address' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `ExcelMysql` -- ALTER TABLE `ExcelMysql` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `ExcelMysql` -- ALTER TABLE `ExcelMysql` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary id';
In this tutorial we will show you how to import excel file data into mysql database using PHP. Generally this process is very hard if you directly import excel file data into mysql database but now by converting excel file into a csv(comma separated value) file then import data into mysql database this becomes easy and time saving here in this tutorial we will follow this step. index.php file show report as follow :
Create following file strucutre :
1-header.php,
2-footer.php,
3-index.php,
1-header.php :
only bootstrap file and js file include in header section,no one extra code is include in header.php files
Code :
<php
/*
*include connection file and fetach if you want record::-
*/
include('connection.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import Excel File Data in MYSQL Database using PHP</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.1.1.js" integrity="sha256-16cdPddA6VdVInumRGo6IbivbERE8p7CQR3HzTBuELA=" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
</head>
<body>
<!-- start:header -->
<nav class="navbar navbar-default navbar-fixed-top">
<div class="container-fluid" id="freeteachnology">
<!-- Brand and toggle get grouped for better mobile display -->
<div class="navbar-header">
<a class="navbar-brand" href="#">Free Teachnology</a>
</div>
<!-- Collect the nav links, forms, and other content for toggling -->
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav">
<li class="active"><a href="#">Home <span class="sr-only"></span></a></li>
<li><a href="#">About us</a></li>
<li><a href="#">Contact us</a></li>
</ul>
</div><!-- /.navbar-collapse -->
</div><!-- /.container-fluid -->
</nav>
2-footer.php :
footer.php files include social link and bootstrap font,please follow following code.
Code :
<!-- Start::footer -->
<footer>
<div class="footer footer-bottom">
<div class="container">
<p class="pull-left"> Copyright © <?php echo date('Y');?>. Design and Develop by - Disuza Jen. </p>
<div class="pull-right">
<ul class="nav nav-pills payments">
<li>
<a target="blank" href="https://www.facebook.com/profile.php?id=100011576444095">
<i class="fa fa-facebook"></i></a>
</li>
<li>
<a target="blank" href="https://plus.google.com/116407769803493244155">
<i class="fa fa-google"></i></a>
</li>
<li>
<a target="blank" href="https://twitter.com/disuzajen1">
<i class="fa fa-twitter"></i></a></li>
<li>
<a target="blank" href="https://plus.google.com/116407769803493244155">
<i class="fa fa-link"></i>
</a>
</li>
</ul>
</div>
</div>
</div>
<!--/.footer-bottom-->
</footer>
<!-- End::footer -->
<link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/font-awesome/4.1.0/css/font-awesome.min.css">
</body>
</htm>
3-connection.php
you have good knowlege of pdo then you can essy understand to code database coonection and other operation of next code.
create database connection using pdo, in this database set database connectivity username,password,host name
Code :
<?php
/*
* Start:: Database connection::-
*/
$hostname="localhost";
$username="root";
$password="";
$dbname="ExcelMysql";
try
{
$db = new PDO("mysql:host=$hostname;dbname=$dbname;charset=utf8mb4", $username, $password);
}
catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
/*
* End:: Database connection::-
*/
?>
if database is not connected then catch through error and execution is stop now.
4-index.php
index.php file include header.php ,connection.php files and footer.php files following code show index file code .
Code :
<?php include('header.php'); ?>
<?php
$stmt = $db->query('SELECT * FROM ExcelMysql order by id DESC');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<body>
<div>
<br><br><br>
<?php
session_start();
if (!empty($_SESSION['statusMsg'])) {
echo '<p>' . $_SESSION['statusMsg'] . '</p>';
unset($_SESSION['statusMsg']);
}?>
<div class="cleafix"></div>
<div class="container">
<div class="alert alert-info">
<strong>Hi..</strong> Import Excel File Data in MYSQL Database using PHP
</div>
<div class="alert alert-warning">
<a href="https://drive.google.com/open?id=0BxmTZPVcu72fWHVLNF9odVZMWmc" class="btn btn-xs btn-warning pull-right" target="blank();">Click</a>
<strong> Code download link-</strong>
</div>
<br><br>
</div>
<div class="container">
<div class="alert alert-success">
<a href="#">Import excel file using csv</a>!
<a class="pull-right" href="addcsv.php"><i class="glyphicon glyphicon-plus"></i></a>
</div>
<div class="alert alert-success">Add Record</a>!
<a class="pull-right" href="add.php"><i class="glyphicon glyphicon-plus"></i></a>
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<?php if (!empty($results)) { ?>
<table class='table table-bordered table-responsive'>
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Contact No</th>
<th>address</th>
<th colspan="2" align="center">Actions</th>
</tr>
<?php
//SQL::fecth all infroamtion of studnet using fetch_assoc array result:
$i = 1;
foreach ($results as $row) {
?>
<tr>
<td><?php print($i); ?></td>
<td><?php print($row['fname']); ?></td>
<td><?php print($row['lname']); ?></td>
<td><?php print($row['email']); ?></td>
<td><?php print($row['mobile']); ?></td>
<td><?php print($row['address']); ?></td>
<td align="center">
<a href="edit.php?edit_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-edit"></i></a>
</td>
<td align="center">
<a onclick="return confirm('Do you want to delete ?')" href="delete.php?delete_id=<?php print($row['id']); ?>"><i class="glyphicon glyphicon-remove-circle"></i></a>
</td>
</tr>
<?php
$i++;
}
?>
</table>
<?php } else {
?>
<div class="alert alert-danger" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> No more record avaliable.
Please click here to <a href="add.php">add </a> record
</div>
<?php } ?>
</div>
<?php include('footer.php'); ?>
above index.php file show database connection and select query of table and show the record history.
5-add.php :
add.php file user can add record using add form submit if validation is correct then submit form and insert record into database.
add.php files form shows as follow :
Code :
<?php include('header.php');?>
<?php
session_start();
if(!empty($_SESSION['statusMsg'])){
echo '<p>'.$_SESSION['statusMsg'].'</p>';
unset($_SESSION['statusMsg']);
}
?>
<body>
<br/><br/><br/><br/>
<div class="container">
<div class="alert alert-success">
<a href="#">PDO in PHP</a>!
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<div class="row col-lg-10">
<div class="panel panel-default user-add-edit">
<div class="panel-heading">Add Student </div>
<ul class="pager">
<li class="previous">
<a href="index.php">← Back</a>
</li>
</ul>
<div class="panel-body">
<form enctype="multipart/form-data" class="form-horizontal" id="excelFileup" name="excelFileup" action="add-action.php" method="post">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">First Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="fname" name="fname" placeholder="First name">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Last Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="lname" name="lname" placeholder="Last name">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Mobile</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="mobile" name="mobile" placeholder="Mobile">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Email</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="email" name="email" placeholder="Email ID">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Address</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="address" name="address" placeholder="Addess">
</div>
</div>
<input type="hidden" name="action_type" value="add"/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" name="submit" value="Submit"/>
<input type="reset" class="btn btn-default" value="Cancel" />
</div>
</div>
</form>
</div>
</div>
</div>
</div>
<?php include('footer.php');?>
6-edit.php :
if user can edit record of particular user then form shows user infroamtion,following simple code is show user record
edit.php files form show as follow :
Code :
<?php include('header.php'); ?>
<?php
$user_id = $_GET['edit_id'];
//
$stmt = $db->query('SELECT * FROM ExcelMysql where id=' . $user_id . '');
$results = $stmt->fetch(PDO::FETCH_ASSOC);
//
session_start();
if (!empty($_SESSION['statusMsg'])) {
echo '<p>' . $_SESSION['statusMsg'] . '</p>';
unset($_SESSION['statusMsg']);
}
?>
<body>
<div class="container">
<div class="alert alert-success">
<a href="#">PDO in PHP</a>!
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<div class="row col-lg-10">
<div class="panel panel-default user-add-edit">
<div class="panel-heading">Add Student </div>
<ul class="pager">
<li class="previous">
<a href="index.php">← Back</a>
</li>
</ul>
<div class="panel-body">
<form class="form-horizontal" action="add-action.php" method="post">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">First Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="fname" name="fname" placeholder="First name" value="<?php print($results['fname']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Last Name</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="lname" name="lname" placeholder="Last name" value="<?php print($results['lname']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Address</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="address" name="address" placeholder="Address" value="<?php print($results['address']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Mobile no</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="mobile" name="mobile" placeholder="Mobile no" value="<?php print($results['mobile']); ?>">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">Email</label>
<div class="col-sm-6">
<input type="text" class="form-control" id="email" name="email" placeholder="Mobile no" value="<?php print($results['email']); ?>">
</div>
</div>
<input type="hidden" name="pk_id" value="<?php print($results['id']); ?>"/>
<input type="hidden" name="action_type" value="edit"/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" name="submit" value="Submit"/>
<input type="reset" class="btn btn-default" value="Cancel" />
</div>
</div>
</form>
</div>
</div>
</div>
</div>
<?php include('footer.php'); ?>
7-addcsv.php :
in this file file browse and click on submit button then file is move on particular folder and read this uploaded file using getcsv() function,this function read all record in file ,add this file html code is :
csv file upload using as follow screen shot shows:
if click on file browser then show file browser dialog :
Code :
<?php include('header.php');?>
<?php
session_start();
if(!empty($_SESSION['statusMsg'])){
echo '<p>'.$_SESSION['statusMsg'].'</p>';
unset($_SESSION['statusMsg']);
}
?>
<body>
<div class="container">
<div class="alert alert-success">
<a href="#">Add record using excel file</a>!
</div>
</div>
<div class="clearfix"></div>
<div class="container">
<div class="row col-lg-10">
<div class="panel panel-default user-add-edit">
<div class="panel-heading">Add Student </div>
<ul class="pager">
<li class="previous">
<a href="index.php">← Back</a>
</li>
</ul>
<div class="panel-body">
<form enctype="multipart/form-data" class="form-horizontal" id="excelFileup" name="excelFileup" action="add-action.php" method="post">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">File Browse</label>
<div class="col-sm-6">
<input type="file" class="form-control" id="csvfile" name="csvfile"> </div>
</div>
<input type="hidden" name="action_type" value="addcsv"/>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<input type="submit" class="btn btn-primary" name="submit" value="Submit"/>
<input type="reset" class="btn btn-default" value="Cancel" />
</div>
</div>
</form>
</div>
</div>
</div>
</div>
<?php include('footer.php');?>
8-add-action.php :
action form submit all record or form content of html files ,if user can submit add user record of then form action files post value get and insert into particular record its code is simple login and very fast understand, you have good Knowles of php and pdo then easily understand following code
Code :
<meta charset="UTF-8">
<?php
/*
* include connection file and fetach if you want record::-
*/
include('connection.php');
session_start();
// echo '<pre>';
// print_r($_POST);
// die;
$action_type = $_POST['action_type'];
$submit = $_POST['submit'];
if ((strcmp($submit, 'Submit') == 0) && trim($action_type) != "addcsv") {
//Post data value::-
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$mobile = $_POST['mobile'];
$email = $_POST['email'];
$address = $_POST['address'];
}
if ((strcmp($submit, 'Submit') == 0) && trim($action_type) == "add") {
$sql = "INSERT INTO ExcelMysql(fname,
lname,
mobile,
email,
address) VALUES (
:fname,
:lname,
:mobile,
:email,
:address)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':fname', $fname, PDO::PARAM_STR);
$stmt->bindParam(':lname', $lname, PDO::PARAM_STR);
$stmt->bindParam(':mobile', $mobile, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->bindParam(':address', $address, PDO::PARAM_STR);
$stmt->execute();
$newId = $db->lastInsertId();
if ($insert_id != "0") {
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully add student infroamtion
</div>';
/* Redirect browser */
header("Location: index.php");
exit();
} else {
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
}
} if ((strcmp($submit, 'Submit') == 0) && trim($action_type) == "edit") {
$pk_id = $_POST['pk_id'];
//SQL:: update record into databse and return last insert id::-
$sql = "UPDATE ExcelMysql SET
fname = :fname,
lname = :lname,
mobile = :mobile,
email = :email,address=:address WHERE id = :id";
$stmt = $db->prepare($sql);
$stmt->bindParam(':fname', $fname, PDO::PARAM_STR);
$stmt->bindParam(':lname', $lname, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->bindParam(':mobile', $mobile, PDO::PARAM_STR);
$stmt->bindParam(':address', $address, PDO::PARAM_STR);
$stmt->bindParam(':id', $pk_id, PDO::PARAM_STR);
$stmt->execute();
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully updated student infroamtion
</div>';
/* Redirect browser */
header("Location: index.php");
exit();
} else if ((strcmp($submit, 'Submit') == 0) && trim($action_type) == "addcsv") {
if(isset($_FILES['csvfile'])){
$errors= array();
$file_name = $_FILES['csvfile']['name'];
$file_size = $_FILES['csvfile']['size'];
$file_tmp = $_FILES['csvfile']['tmp_name'];
$file_type = $_FILES['csvfile']['type'];
$file_ext=strtolower(end(explode('.',$_FILES['csvfile']['name'])));
$expensions= array("csv");
if(in_array($file_ext,$expensions)=== false){
$errors[]="extension not allowed, please choose a CSV file.";
}
if($file_size > 2097152) {
$errors[]='File size must be excately 2 MB';
}
if(empty($errors)==true) {
move_uploaded_file($file_tmp,"uploads/".$file_name);
echo "Success";
}else{
print_r($errors);
}
}
$file_names='uploads/'.$file_name;
// $file_names='uploads/demo.csv';
$handle = fopen($file_names, "r");
$filesop = fgetcsv($handle, 1000, ",");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
echo $fname = $filesop[0];
$lname = $filesop[1];
$mobile = $filesop[2];
$email = $filesop[3];
$address = $filesop[4];
$sql = "INSERT INTO ExcelMysql(fname,
lname,
mobile,
email,
address) VALUES (
:fname,
:lname,
:mobile,
:email,
:address)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':fname', $fname, PDO::PARAM_STR);
$stmt->bindParam(':lname', $lname, PDO::PARAM_STR);
$stmt->bindParam(':mobile', $mobile, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->bindParam(':address', $address, PDO::PARAM_STR);
$stmt->execute();
$newId = $db->lastInsertId();
}
$_SESSION['statusMsg'] = '<div class="alert alert-info errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Well done!</strong> You successfully insert record student infroamtion
</div>';
/* Redirect browser */
header("Location: index.php");
exit();
}else
{
$_SESSION['statusMsg'] = '<div class="alert alert-danger errorMsg" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<strong>Sorry!</strong> Something goes wrong.
</div>';
/* Redirect browser */
header("Location: add.php");
exit();
}
?>
now above example is execute and very fast to produce .sql file, no need to extra functionality you can do or no need login into mysql database.
if you like my blog please comment and share as well as like my blog.
thanks.
Comments