In this tutorial Extract Data in Excel From MySQL database using PHP in Specific Date Range, we are going to learn how to export MySQL data to excel using PHP and HTML. We generally need reports in excel sheet, but sometimes our data stored in MySQL database. Then It is quite easy to fetch records from MySQL and export the result in a .csv file or .xls file and provider button for a user to download the data according to the date or between two dates. In this tutorial will make use of PHP and MySQL to do this functionality. This downloading excel report or data option is very important in a web application. Using this code, we can extract the data from MySQL database.
You can also check
[adsense_hint]
Creating Our Database
Firstly, we have to create a database to store the data, for creating database follow these steps:
- Start the server
- Open phpMyAdmin
- Select the database and Create a new database with name “excel “after that
- Create the table with the table name “excel”
- And then insert the details Or
- After creating a database name, click the SQL and paste the following code.
CREATE TABLE IF NOT EXISTS `excel` ( `id` int(12) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `mobile_no` varchar(255) NOT NULL, `address` varchar(2000) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; INSERT INTO `excel` (`id`, `first_name`, `last_name`, `mobile_no`, `address`, `date`) VALUES (1, 'Abhishek', 'Kumar', '9876543210', 'Delhi', '2018-04-01 07:00:00'), (2, 'Jamaley', 'Hussain', '7894561230', 'Ranchi', '2018-04-02 07:00:00'), (3, 'Abhinay', 'Kumar', '1234567890', 'Pune', '2018-04-03 07:00:00'), (4, 'Ram', 'Raghav', '6789065432', 'Patna', '2018-04-04 07:00:00'), (5, 'Aman', 'Kumar', '123456123456', 'Kota', '2018-04-04 07:00:00'), (6, 'Raghav', 'Singh', '09876567892', 'Punjab', '2018-04-06 07:00:00');
This is the MySQL database table which stores all the data.
Connection Page
Here is a connection.php file, it will contain our script to the database. And it helps to connect to the server and use our database.
Connection.php
<?php
$conn = mysql_connect('localhost','root','');
mysql_select_db(‘excel');
?>
Display Page
Index.php
This page will appear whenever a user opens the site, this page contains simple web interface which contains two date input field that allows the user enter the first and last date to extract the data from the database in excel according to date.
Now, Create a PHP file named “index.php” and paste the following code inside of it.
<html>
<head>
<title>Techjunkgigs</title>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?libraries=places&language=en"></script>
<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.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div class="Container">
<h1>Ware Dump</h1>
<div>
<form method="POST" action= "excel.php">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title"><b>Select Date Between</b></h4>
</div>
<div class="modal-body">
<div class="form-group">
<input type="date" class="form-control" id="date1" name="date1" required/>
</div>
<div class="form-group">
<label for="attribute2" class="control-label"> To</label>
<input type="date" class="form-control" id="date2" name="date2" required/>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<button type="submit" class="btn btn-Primary" name="submit">Submit</a></button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
When the user enters the date and clicks to the submit button the index.php page will redirect to logic page (excel.php) and extract the data from the database and download it.
Logic Page
This page contains the logic and the MySQL query to fetch the data from the database
Excel.php
<?php
include('connection.php');
if(isset($_POST['submit'])){
$date1=$_POST['date1'];
$date2=$_POST['date2'];
$SQL = "SELECT *
FROM excel
where DATE(date) BETWEEN '$date1' AND '$date2'";
$header = '';
$result ='';
$exportData = mysql_query ($SQL ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $exportData );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $exportData , $i ) . "\t";
}
while( $row = mysql_fetch_row( $exportData ) )
{
$line = '';
foreach( $row as $value )
{
if ( ( !isset( $value ) ) || ( $value == "" ) )
{
$value = "\t";
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$result .= trim( $line ) . "\n";
}
$result = str_replace( "\r" , "" , $result );
if ( $result == "" )
{
$result = "\nNo Record(s) Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$result";
}
?>
Demo Image
That’s all, this is how we can export MySQL data to excel using simple PHP and HTML code. You can also customize this code further as per your requirement. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. And please feel free to ask any question regarding the tutorial.