How to Filter Between Two Dates using jQuery with PHP/MySQLi

Submitted by nurhodelta_17 on Monday, April 2, 2018 - 10:47.

Getting Started

First, we need the jQuery library and also Bootstrap for a better design to our app. I've included these files in the downloadable of this tutorial but if you want, you can download them yourself using the links below: For Bootstrap For jQuery Take note that we are gonna be using HTML5's input date.

Creating our Database

Next, we create the database that we are going to filter in this tutorial. I've included a SQL file in the downloadable of this tutorial. All you have to do is import the said file. If you have no idea on how to import, please visit my tutorial How import .sql file to restore MySQL database.

Displaying our Table

Next, we are going to display table where we display our data. Create a new file, name it as index.html and paste the codes below.

<!DOCTYPE html>
<html>
<head>
        <meta charset="utf-8">
        <title>How to Filter Between Two Dates using jQuery with PHP/MySQLi</title>
        <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
</head>
<body>
<div class="container">
        <h1 class="page-header text-center">Filter Between Two Dates using jQuery</h1>
        <div class="row">
                <div class="col-sm-8 col-sm-offset-2">
                        <form class="form-inline">
                                <input type="date" class="form-control" id="date_start" required>
                                <input type="date" class="form-control" id="date_end" required>
                                <button type="button" id="filter" class="btn btn-primary">Filter</button>
                                <button type="button" id="return" class="btn btn-primary">Return</button>
                        </form>
                        <br>
                        <table class="table table-bordered table-striped">
                                <thead>
                                        <th>ID</th>
                                        <th>Firstname</th>
                                        <th>Lastname</th>
                                        <th>Address</th>
                                        <th>Gender</th>
                                        <th>Birthday</th>
                                </thead>
                                <tbody id="tbody">
                                </tbody>
                        </table>
                </div>
        </div>
</div>
 
<script src="jquery.min.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>
<script src="app.js"></script>
</body>
</html>

Creating our jQuery Scripts

Next, we create our jquery scripts for all our jquery functions and request. Create a new file, name it as app.js and paste the codes below.

$(document).ready(function(){
        fetch();
 
        //clicking the filter button
        $('#filter').click(function(e){
                var date_start = $('#date_start').val();
                var date_end = $('#date_end').val();
                $.ajax({
                        method: 'POST',
                        url: 'filter.php',
                        data: {
                                date_start: date_start,
                                date_end: date_end,
                        },
                        dataType: 'json',
                        success: function(response){
                                if(response.error){
                                        $('#tbody').html('<tr><td colspan="6" align="center">No data matches your filter</td></tr>');
                                }
                                else{
                                        $('#tbody').html(response.data);
                                }
                        }
                });
        });
 
        $('#return').click(function(){
                $('#date_start').val('');
                $('#date_end').val('');
                fetch();
        });
 
});
function fetch(){
        $.ajax({
                method: 'POST',
                url: 'fetch.php',
                dataType: 'json',
                success: function(response){
                        $('#tbody').html(response);
                }
        });
}

Creating our Fetch Table Data Script

Next, we create our script that will fetch data from our MySQL Database as our initial table data. Create a new file, name it as fetch.php.

<?php
        //connection
        $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
        $output = '';
 
        $sql = "SELECT * FROM members";
        $query = $conn->query($sql);
 
        while($row = $query->fetch_assoc()){
                $output .= "
                        <tr>
                                <td>".$row['id']."</td>
                                <td>".$row['firstname']."</td>
                                <td>".$row['lastname']."</td>
                                <td>".$row['address']."</td>
                                <td>".$row['gender']."</td>
                                <td>".date('M d, Y', strtotime($row['birthday']))."</td>
                        </tr>
                ";
        }
 
        echo json_encode($output);
        
?>

Creating our Filter Script

Lastly, we create our script that will filter data on our table depending on the dates we enter. We name this file as filter.php.

<?php
        //connection
        $conn = new mysqli('localhost', 'root', '', 'mydatabase');
 
        $start = date('Y-m-d', strtotime($_POST['date_start']));
        $end = date('Y-m-d', strtotime($_POST['date_end']));
        
        $output = array('error' => false, 'data' => '');
 
        $sql = "SELECT * FROM members WHERE birthday BETWEEN '$start' AND '$end'";
        $query = $conn->query($sql);
 
        if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
                        $output['data'] .= "
                                <tr>
                                        <td>".$row['id']."</td>
                                        <td>".$row['firstname']."</td>
                                        <td>".$row['lastname']."</td>
                                        <td>".$row['address']."</td>
                                        <td>".$row['gender']."</td>
                                        <td>".date('M d, Y', strtotime($row['birthday']))."</td>
                                </tr>
                        ";
                }
        }
        else{
                $output['error'] = true;
        }
 
        echo json_encode($output);
?>

That ends this tutorial. Happy Coding :)

Add new comment

641 views