Filter records by Date range with Vue and PHP

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 in 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.


1. Table structure

I am using employees table in the tutorial example. I added some records in 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. Configuration

Create a new config.php file.

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 Axios package to send AJAX request. I have stored in the project directory. 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 which 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 an 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 pass fromdate: this.fromdate and todate: this.todate as data.

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 vuejsDatepicker to 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. 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.

Related Posts