Date range filter without datepicker sometimes gets complex and there is always a possibility that the user specifies the date in a different format. In this case, need to handle it either on the client-side or server-side script.
By allowing datepicker the user only needs to pick a date in a given format.
Date filter helps to generate a report and view records between specific dates.
To add datepicker I am using datepicker Vue component in the example.
In this tutorial, I show how you can filter records by date with Vue.js and PHP.
Contents
1. Table structure
I am using employees table in the tutorial example. I added some records to it.
CREATE TABLE `employees` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `gender` varchar(10) NOT NULL, `date_of_join` date NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Database Configuration
Create a config.php file for the database configuration.
Completed Code
<?php
session_start();
$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. HTML
Adding datepicker using <vuejs-datepicker > component.
For this include –
<script src="https://unpkg.com/vuejs-datepicker"></script>
and I am using the Axios package to send AJAX request. I have stored it in the project folder. You can download it from here.
<script src='axios-master/dist/axios.min.js'></script>
Add 2 <vuejs-datepicker > for from and to date. Set date format to “dd/MM/yyyy” using format and add remove icon using :clear-button="true" to clear selected date. Set v-model and add closed event which calls checkDate(). This event gets called after a date is been selected.
Add a button that calls fetchRecords() method on click.
Use <table > to list records from employees Object.
With v-show="recordNotFound" display “No record found.” <tr> if employees Object is empty.
Completed Code
<script src="vue.js"></script>
<script src='axios-master/dist/axios.min.js'></script>
<script src="https://unpkg.com/vuejs-datepicker"></script>
<style type="text/css">
.inline{
display: inline-block;
}
</style>
<div id='myapp'>
<!-- Date picker -->
<vuejs-datepicker wrapper-class="inline" placeholder="From date" format="dd/MM/yyyy" :clear-button="true" v-model='fromdate' @closed='checkDate();'></vuejs-datepicker>
<vuejs-datepicker wrapper-class="inline" placeholder="To date" format="dd/MM/yyyy" :clear-button="true" v-model='todate' @closed='checkDate();' ></vuejs-datepicker>
<!-- Search Button -->
<input type='button' @click='fetchRecords()' value='Search'>
<br><br>
<!-- List records -->
<table border='1' width='80%' style='border-collapse: collapse;'>
<thead>
<tr>
<th>Employee name</th>
<th>Date of Join</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<tr v-for='employee in employees'>
<td>{{ employee.emp_name }}</td>
<td>{{ employee.date_of_join }}</td>
<td>{{ employee.email }}</td>
</tr>
<tr v-show="recordNotFound">
<td colspan='3'>No record found.</td>
</tr>
</tbody>
</table>
</div>
4. PHP
Create ajaxfile.php file.
If $_GET['fromdate'] and $_GET['todate'] is not empty then prepare date search query on date_of_join field and assign in $condition variable. Use between to select records.
Fetch records from employees table.
Loop on the fetched records and assign values to $response Array.
Return $response Array in JSON format.
Completed Code
<?php
include "config.php";
$condition = "1";
if( (isset($_GET['fromdate']) && $_GET['fromdate'] != '' ) &&
(isset($_GET['todate']) && $_GET['todate'] != '' ) ){
$condition = " date_of_join between '".$_GET['fromdate']."' and '".$_GET['todate']."' ";
}
$userData = mysqli_query($con,"select * from employees WHERE ".$condition );
$response = array();
while($row = mysqli_fetch_assoc($userData)){
$response[] = array(
"id"=>$row['id'],
"emp_name" => $row['emp_name'],
"date_of_join" => $row['date_of_join'],
"email" => $row['email']
);
}
echo json_encode($response);
exit;
5. Script
Initialize Vue on #myapp selector.
- data – Define 4 variables –
- fromdate – For from date.
- todate – For to date.
- employees – To store employees records.
- recordNotFound – Assign true. Use to hide and show “No record found.” <tr>.
- methods – Create 2 methods –
- checkDate – This method call on date selection. Check if todate is greater than fromdate. If not then assign fromdate value to todate.
- fetchRecords – Check if from and to date is been selected. If selected then send AJAX request to
'ajaxfile.php'and passfromdate: this.fromdateandtodate: this.todateasdata.
On successful callback assign response.data to app.employees.
If response.data is empty then assign true to app.recordNotFound for displaying “No record found.” row.
- component – Pass
vuejsDatepickerto enable datepicker.
Completed Code
var app = new Vue({
el: '#myapp',
data: {
fromdate: "",
todate: "",
employees: "",
recordNotFound: true
},
methods: {
checkDate: function(){
if(this.fromdate != ''){
var fromdate = new Date(this.fromdate);
var todate = new Date(this.todate);
if(fromdate.getTime() > todate.getTime()){
var currentDate = new Date();
var day = fromdate.getDate();
var month = fromdate.getMonth();
var year = fromdate.getFullYear();
this.todate = new Date(year, month, day);
}
}
},
fetchRecords: function(){
if(this.fromdate !='' && this.todate != ''){
axios.get('ajaxfile.php', {
params: {
fromdate: this.fromdate,
todate: this.todate
}
})
.then(function (response) {
app.employees = response.data;
// Display no record found <tr> if record not found
if(app.employees.length == 0){
app.recordNotFound = true;
}else{
app.recordNotFound = false;
}
})
.catch(function (error) {
console.log(error);
});
}
}
},
components: {
vuejsDatepicker
}
})
6. Demo
7. Conclusion
To use <vuejs-datepicker> in your page you need to define vuejsDatepicker in components option. Use the model to read values and pass in the AJAX request to fetch records.
You can learn more about this component from here.
If you found this tutorial helpful then don't forget to share.
