CSV file is been used for data import, export, and generating a report.
If you have huge data available in the MySQL database and you only require a specific date range data but the file contains all records and you need it on your own.
By adding a date filter to the form you just need to pick the range and export it.
In this tutorial, I show how you can export MySQL database data in CSV format by date range with PHP. I am using jQuery UI for datepicker.
Contents
- Table structure
- Configuration
- Download and Include
- HTML and PHP
- Create and Download CSV file
- jQuery
- Demo
- Conclusion
1. Table structure
I am using employee table in the example.
CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(30) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(70) NOT NULL, `date_of_joining` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Create a new config.php to define database connection.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Download and Include
- Download jQuery and jQuery UI libraries.
- Include jquery-ui.css, jQuery library, and jquery-ui.min.js script.
<!-- jQuery UI CSS --> <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css"> <!-- jQuery --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <!-- jQuery UI JS --> <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
4. HTML and PHP
Create a <form method='post' action='download.php' >. Add two text elements and a submit button.
The text element is used for the date picker.
I created <table > to list all records of the employee table.
Completed Code
<div >
<form method='post' action='download.php'>
<!-- Datepicker -->
<input type='text' class='datepicker' placeholder="From date" name="from_date" id='from_date' readonly>
<input type='text' class='datepicker' placeholder="To date" name="to_date" id='to_date' readonly>
<!-- Export button -->
<input type='submit' value='Export' name='Export'>
</form>
<table border='1' style='border-collapse:collapse;'>
<tr>
<th>ID</th>
<th>Employee Name</th>
<th>Salary</th>
<th>Gender</th>
<th>City</th>
<th>Email</th>
<th>Date of joining</th>
</tr>
<?php
$query = "SELECT * FROM employee ORDER BY id asc";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_assoc($result)){
$id = $row['id'];
$emp_name = $row['emp_name'];
$salary = $row['salary'];
$gender = $row['gender'];
$city = $row['city'];
$email = $row['email'];
$date_of_joining = $row['date_of_joining'];
?>
<tr>
<td><?= $id; ?></td>
<td><?= $emp_name; ?></td>
<td><?= $salary; ?></td>
<td><?= $gender; ?></td>
<td><?= $city; ?></td>
<td><?= $email; ?></td>
<td><?= $date_of_joining; ?></td>
</tr>
<?php
}
?>
</table>
</div>
5. Create and Download CSV file
Create download.php file to create CSV and download it.
Read POST from_date and to_date and assign it to the variables.
Create a SELECT query to fetch records from employee table. By default select all records if from_date and to_date are not set.
If both set then use between on date_of_joining in WHERE clause to select records.
Open file in write mode and write header columns in it.
Loop on the fetched records and initialize $employee_arr Array with required values and write in the file.
After successfully file creation prepare it for download and delete it after downloading using unlink() method.
Completed Code
<?php
include "config.php";
$filename = 'employee_'.time().'.csv';
// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];
// Select query
$query = "SELECT * FROM employee ORDER BY id asc";
if(isset($_POST['from_date']) && isset($_POST['to_date'])){
$query = "SELECT * FROM employee where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}
$result = mysqli_query($con,$query);
$employee_arr = array();
// file creation
$file = fopen($filename,"w");
// Header row - Remove this code if you don't want a header row in the export file.
$employee_arr = array("id","Employee Name","Salary","Gender","City","Email","Date of Joining");
while($row = mysqli_fetch_assoc($result)){
$id = $row['id'];
$emp_name = $row['emp_name'];
$salary = $row['salary'];
$gender = $row['gender'];
$city = $row['city'];
$email = $row['email'];
$date_of_joining = $row['date_of_joining'];
// Write to file
$employee_arr = array($id,$emp_name,$salary,$gender,$city,$email,$date_of_joining);
fputcsv($file,$employee_arr);
}
fclose($file);
// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; ");
readfile($filename);
// deleting file
unlink($filename);
exit();
6. jQuery
Initialize jQuery UI datepicker on #from_date and #to_date. Set the date format to "yy-mm-dd" and enable year change.
Add validation on date selection using onSelect option.
If from date is gets selected then not allow to date should be less than from date by setting minDate option of #to_date similarly, if to date gets selected then not allow from date to be greater than to date by setting maxDate option of #from_date.
Completed Code
$(document).ready(function(){
// From datepicker
$("#from_date").datepicker({
dateFormat: 'yy-mm-dd',
changeYear: true,
onSelect: function (selected) {
var dt = new Date(selected);
dt.setDate(dt.getDate() + 1);
$("#to_date").datepicker("option", "minDate", dt);
}
});
// To datepicker
$("#to_date").datepicker({
dateFormat: 'yy-mm-dd',
changeYear: true,
onSelect: function (selected) {
var dt = new Date(selected);
dt.setDate(dt.getDate() - 1);
$("#from_date").datepicker("option", "maxDate", dt);
}
});
});
7. Demo
8. Conclusion
POST the date search values and use between to SELECT records. Use fputcsv() function to write data in the file.
If you want to know how to import CSV file data into a MySQL database table you can view my earlier tutorial.
If you found this tutorial helpful then don't forget to share.