How to Filter Range of Date With PHP MySQLi Tutorial

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

We will be using PHP as a scripting language and interpreter that is used primarily on any web server including XAMPP, WAMP, etc. It is being used in any famous 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 locate the database file inside the folder of the application then click ok.

tut1

Or you can simply do it programmatically. To do that, in your PHPMyAdmin, navigate to your database to the SQL Tab and paste the SQL Script below. Then, click the Go Button to finally created your database table and insert the sample data.

  1. CREATE TABLE `member` (
  2. `firstname` varchar(50) NOT NULL,
  3. `lastname` varchar(50) NOT NULL,
  4. `project` varchar(100) NOT NULL,
  5. `date_submit` date NOT NULL
  6.  
  7. INSERT INTO `member` (`mem_id`, `firstname`, `lastname`, `project`, `date_submit`) VALUES
  8. (1, 'John', 'Smith', 'The Smiling', '2021-09-16'),
  9. (2, 'Claire ', 'Temple', 'The Peace', '2021-09-22');

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.

  1. <?php
  2. $conn=mysqli_connect("localhost", "root", "", "db_range");
  3.  
  4. if(!$conn){
  5. die("Error: Failed to connect to database!");
  6. }
  7. ?>

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.

  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4. <meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
  5. <link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
  6. </head>
  7. <body>
  8. <nav class="navbar navbar-default">
  9. <div class="container-fluid">
  10. <a href="https://sourcecodester.com" class="navbar-brand">Sourcecodester</a>
  11. </div>
  12. </nav>
  13. <div class="col-md-3"></div>
  14. <div class="col-md-6 well">
  15. <h3 class="text-primary">PHP - Filter Range Of Date With MySQLi</h3>
  16. <hr style="border-top:1px dotted #000;"/>
  17. <form class="form-inline" method="POST" action="">
  18. <label>Date:</label>
  19. <input type="date" class="form-control" placeholder="Start" name="date1" value="<?php echo isset($_POST['date1']) ? $_POST['date1'] : '' ?>" />
  20. <label>To</label>
  21. <input type="date" class="form-control" placeholder="End" name="date2" value="<?php echo isset($_POST['date2']) ? $_POST['date2'] : '' ?>"/>
  22. <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>
  23. </form>
  24. <br /><br />
  25. <div class="table-responsive">
  26. <table class="table table-bordered">
  27. <thead class="alert-info">
  28. <tr>
  29. <th>Firstname</th>
  30. <th>Lastname</th>
  31. <th>Project</th>
  32. <th>Date Submit</th>
  33. </tr>
  34. </thead>
  35. <tbody>
  36. <?php include'range.php'?>
  37. </tbody>
  38. </table>
  39. </div>
  40. </div>
  41. </body>
  42. </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.

  1. <?php
  2. require 'conn.php';
  3. if(ISSET($_POST['search'])){
  4. $date1 = date("Y-m-d", strtotime($_POST['date1']));
  5. $date2 = date("Y-m-d", strtotime($_POST['date2']));
  6. $query=mysqli_query($conn, "SELECT * FROM `member` WHERE date(`date_submit`) BETWEEN '$date1' AND '$date2'") or die(mysqli_error());
  7. $row=mysqli_num_rows($query);
  8. if($row>0){
  9. while($fetch=mysqli_fetch_array($query)){
  10. ?>
  11. <tr>
  12. <td><?php echo $fetch['firstname']?></td>
  13. <td><?php echo $fetch['lastname']?></td>
  14. <td><?php echo $fetch['project']?></td>
  15. <td><?php echo $fetch['date_submit']?></td>
  16. </tr>
  17. <?php
  18. }
  19. }else{
  20. echo'
  21. <tr>
  22. <td colspan = "4"><center>Record Not Found</center></td>
  23. </tr>';
  24. }
  25. }else{
  26. $query=mysqli_query($conn, "SELECT * FROM `member`") or die(mysqli_error());
  27. while($fetch=mysqli_fetch_array($query)){
  28. ?>
  29. <tr>
  30. <td><?php echo $fetch['firstname']?></td>
  31. <td><?php echo $fetch['lastname']?></td>
  32. <td><?php echo $fetch['project']?></td>
  33. <td><?php echo $fetch['date_submit']?></td>
  34. </tr>
  35. <?php
  36. }
  37. }
  38. ?>

DEMO VIDEO

There you have it we successfully created Filter Range Of Date With MySQLi using PHP. I hope that this simple tutorial helps you to what you are looking for. For more updates and tutorials just kindly visit this site.

I have also uploaded the working source code I created for this tutorial. You can download it for free. Just click the download button located below this article.

Enjoy Coding!

Add new comment