How To Export CSV Data With Date Range Filter PHP
In this article, we will see how to export CSV data with a date range filter in PHP. Here, we will learn about the date range filter with export data from the MySQL database in PHP. So, you can filter records for specific date duration and export those records.
Also, we will use a jquery date range picker to export CSV file data. So, using jquery date range picker you can quickly get records from specific date ranges in PHP.
So, let's see PHP export CSV data with a date range filter, export CSV file with a date range filter, and how to filter records using date range in PHP.
In this step, we will create a user table. So, add the following code and run the below code.
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
`email` varchar(50) NOT NULL,
`gender` varchar(10) NOT NULL,
`city` varchar(80) NOT NULL,
`date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now, we will create a config.php file and add the database configuration.
<?php
$host = "localhost";
$user = "root";
$password = "password";
$dbname = "export_csv_example";
$con = mysqli_connect($host, $user, $password, $dbname);
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
In this step, we will create an index.php file. Also, we will add a date range filter and also we will display records from the database.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"> -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<title>How To Export CSV Data With Date Range Filter PHP - Techsolutionstuff</title>
</head>
<body>
<div class="container">
<div >
<form method='post' action='download_csv.php'>
<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>
<input type='submit' value='Export' name='Export'>
</form>
<table border='1' style='border-collapse:collapse;'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>City</th>
<th>Date of Joining</th>
</tr>
<?php
$query = "SELECT * FROM users ORDER BY id asc";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_assoc($result)){
$id = $row['id'];
$name = $row['name'];
$email = $row['email'];
$gender = $row['gender'];
$city = $row['city'];
$date_of_joining = $row['date_of_joining'];
?>
<tr>
<td><?= $id; ?></td>
<td><?= $name; ?></td>
<td><?= $email; ?></td>
<td><?= $gender; ?></td>
<td><?= $city; ?></td>
<td><?= $date_of_joining; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>
<script>
$(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);
}
});
});
</script>
Now, we will create a download_csv.php file. In this file, we will create a CSV file and download it. Also, we will filter records using from date and to date. So, add the following code to that file.
<?php
include "config.php";
$filename = 'users_'.time().'.csv';
// POST values
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];
// Select query
$query = "SELECT * FROM users ORDER BY id asc";
if(isset($_POST['from_date']) && isset($_POST['to_date'])){
$query = "SELECT * FROM users where date_of_joining between '".$from_date."' and '".$to_date."' ORDER BY id asc";
}
$result = mysqli_query($con, $query);
$users_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.
$users_arr = array("id", "Name", "Email", "Gender","City", "Date of Joining");
while($row = mysqli_fetch_assoc($result)){
$id = $row['id'];
$name = $row['name'];
$email = $row['email'];
$gender = $row['gender'];
$city = $row['city'];
$date_of_joining = $row['date_of_joining'];
// Write to file
$users_arr = array($id, $name, $email, $gender, $city, $date_of_joining);
fputcsv($file,$users_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();
You might also like:
- Read Also: How To Add Bootstrap 5 Datepicker
- Read Also: Jquery Search Filter Table Rows Example
- Read Also: Datatable Filter With Dropdown In Laravel 9
- Read Also: How To Get Current Date And Time In Node.js
