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 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
2. Database Configuration
3. HTML
4. PHP
5. Script
6. Demo
7. Conclusion

---

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

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