Filter Range Of Date With MySQL using PHP

By CampCodes Administrator

Updated on:

filter range of date with mysql using php

In this tutorial, we will create a Filter Range Of Date With MySQLi using PHP. This code can filter a range of table row from MySQLi when the user provides two dates from inputs. The code use MySQLi SELECT query to filter a variety of data in the MySQL row by providing two dates in the WHERE clause by adding a parameter BETWEEN. This a user-friendly program feel free to modify and use it to your system.

We will be using PHP as a scripting language and interpreter that is mainly used on any web server, including xamp, wamp, etc. It is being applied to any popular websites because of the modern approach as its today.

Getting Started:

First, you have to download & install XAMPP or any local server that run PHP scripts. Here’s the link for XAMPP server https://www.apachefriends.org/index.html.

And, this is the link for the bootstrap that I used for the layout design https://getbootstrap.com/.

Creating Database

Open your database web server then create a database name in it db_range, after that click Import then locates the database file inside the folder of the application then click ok.

filter range of date with mysql using php

Creating the database connection

Open your any kind of text editor(notepad++, etc..). Then just copy/paste the code below then name it conn.php.

<?php
    $conn=mysqli_connect("localhost", "root", "", "db_range");
 
    if(!$conn){
        die("Error: Failed to connect to database!");
    }
?>

Creating The Interface

This is where we will create a simple form for our application. To create the forms simply copy and write it into your text editor, then save it as index.php.

READ ALSO:   Create a Simple Search Box in PHP MySQL

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
        <link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
    </head>
<body>
    <nav class="navbar navbar-default">
        <div class="container-fluid">
            <a href="https://sourcecodester.com" class="navbar-brand">Sourcecodester</a>
        </div>
    </nav>
    <div class="col-md-3"></div>
    <div class="col-md-6 well">
        <h3 class="text-primary">PHP - Filter Range Of Date With MySQLi</h3>
        <hr style="border-top:1px dotted #000;"/>
        <form class="form-inline" method="POST" action="">
            <label>Date:</label>
            <input type="date" class="form-control" placeholder="Start"  name="date1"/>
            <label>To</label>
            <input type="date" class="form-control" placeholder="End"  name="date2"/>
            <button class="btn btn-primary" name="search"><span class="glyphicon glyphicon-search"></span></button> <a href="index.php" type="button" class="btn btn-success"><span class = "glyphicon glyphicon-refresh"><span></a>
        </form>
        <br /><br />
        <div class="table-responsive">	
            <table class="table table-bordered">
                <thead class="alert-info">
                    <tr>
                        <th>Firstname</th>
                        <th>Lastname</th>
                        <th>Project</th>
                        <th>Date Submit</th>
                    </tr>
                </thead>
                <tbody>
                    <?php include'range.php'?>	
                </tbody>
            </table>
        </div>	
    </div>
</body>
</html>

Creating the Main Function

This code contains the main function of the application. This code will filter a range of data when the button is clicked. To do that just copy and write this block of codes inside the text editor, then save it as range.php.

<?php
    require'conn.php';
    if(ISSET($_POST['search'])){
        $date1 = date("Y-m-d", strtotime($_POST['date1']));
        $date2 = date("Y-m-d", strtotime($_POST['date2']));
        $query=mysqli_query($conn, "SELECT * FROM `member` WHERE `date_submit` BETWEEN '$date1' AND '$date2'") or die(mysqli_error());
        $row=mysqli_num_rows($query);
        if($row>0){
            while($fetch=mysqli_fetch_array($query)){
?>
    <tr>
        <td><?php echo $fetch['firstname']?></td>
        <td><?php echo $fetch['lastname']?></td>
        <td><?php echo $fetch['project']?></td>
        <td><?php echo $fetch['date_submit']?></td>
    </tr>
<?php
            }
        }else{
            echo'
            <tr>
                <td colspan = "4"><center>Record Not Found</center></td>
            </tr>';
        }
    }else{
        $query=mysqli_query($conn, "SELECT * FROM `member`") or die(mysqli_error());
        while($fetch=mysqli_fetch_array($query)){
?>
    <tr>
        <td><?php echo $fetch['firstname']?></td>
        <td><?php echo $fetch['lastname']?></td>
        <td><?php echo $fetch['project']?></td>
        <td><?php echo $fetch['date_submit']?></td>
    </tr>
<?php
        }
    }
?>
data table date range filter example datatable date range filter datatable date range filter not working datatable with date range filter in php codeigniter datatables column filter datepicker datatables date range filter example datatables daterangepicker date between in mysql query with example date between two dates in mysql filter data by date in php filter range of date in php filter range of date using between query in php mysql filter range of date with mysql using php how to check date between two dates in mysql how to fetch data date wise from database in php how to insert current date in mysql using php how to search the records between two dates using php codeigniter mysql between date mysql between dates mysql between dates inclusive mysql between dates query mysql between time mysql between two dates example mysql between two numbers mysql date between mysql date between two columns mysql date between two dates mysql date between two dates example mysql date format mysql date functions mysql date query mysql date query between two dates mysql date range interval mysql date range query mysql date type mysql datetime mysql datetime format mysql datetime functions mysql filter by date mysql interval mysql loop through date range mysql query between two dates mysql query date range between mysql range function mysql select all dates in a range even if no records present mysql select time between two dates mysql where between dates php insert date into mysql php mysql date range search with jquery datepicker php tutorials search by date in php mysql select data between two dates in codeigniter select data between two dates in mysql select data between two dates in mysql php sql between datetime sql query between two dates and times sql query to get data between two dates sql query to get data between two months sql query to get records between two dates sql where date start date and end date query in mysql

Leave a Comment