1. Tutorials
  2. PHP Tutorial
  3. Retrieving Data Between 2 Dates From Database using PHP and MySQL DB Tutorial

Retrieving Data Between 2 Dates From Database using PHP and MySQL DB Tutorial

Submitted by oretnom23 on Friday, May 12, 2023 - 13:49.

In this tutorial, you can learn to Retrieve Data Between 2 Dates from the Database using PHP Language and MySQL Database. The tutorial aims to provide students and beginners with a reference for learning some techniques for developing dynamic websites or web applications. Here, I will be providing simple web application scripts that demonstrate the objective of this tutorial. The sample source code zip file is free to download.

What is Retrieving Data Between 2 Dates From the Database mean?

Retrieving Data between 2 dates from the database is a way of fetching data from the database and displaying it on the front end. This kind of data retrieving limits or filters the data to fetch between the 2 specific dates.

How to Retrieve Data Between 2 Dates from the Database?

To retrieve data between 2 dates using PHP and MySQL Database, we simply need a source to get the date range we needed to filter the data. In some cases, we can add it directly to the SQL Query and in some cases we allow users to choose their own custom dates to use for filtering the data. For dynamic filtering data, we can simply add 2 input fields for the from and to dates. The 2 input fields will be used by the users to input their desired dates. Then, we can simply create an SQL statement that targets and filters only the data whose date column is within the range of the given dates. Check out the simple web application scripts that I created and provided below to understand it more.

Sample Web App Scripts

The following scripts below result in a simple web application page that display the members' information listed and stored on the application database. It has 2 input fields that allow the users to limit or filter the data to show only specific dates.

Database Schema

In this sample web application, I am using the following database schema, and the database is known as dummy_db.

  1. -- phpMyAdmin SQL Dump
  2. -- version 5.1.3
  3. -- https://www.phpmyadmin.net/
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: May 12, 2023 at 05:13 AM
  7. -- Server version: 10.4.24-MariaDB
  8. -- PHP Version: 8.1.5
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  12. SET time_zone = "+00:00";
  13.  
  14. --
  15. -- Database: `dummy_db`
  16. --
  17.  
  18. -- --------------------------------------------------------
  19.  
  20. --
  21. -- Table structure for table `members`
  22. --
  23.  
  24. CREATE TABLE `members` (
  25.     `id` mediumint(8) UNSIGNED NOT NULL,
  26.     `created_date` date DEFAULT NULL,
  27.     `name` varchar(255) DEFAULT NULL,
  28.     `phone` varchar(100) DEFAULT NULL,
  29.     `email` varchar(255) DEFAULT NULL
  31.  
  32. --
  33. -- Dumping data for table `members`
  34. --
  35.  
  36. INSERT INTO `members` (`id`, `created_date`, `name`, `phone`, `email`) VALUES
  37. (1, '2023-05-25', 'Herman Cardenas', '(384) 903-4561', '[email protected]'),
  38. (2, '2022-09-24', 'Macaulay Merrill', '1-354-557-2230', '[email protected]'),
  39. (3, '2024-03-16', 'Yuri Yang', '1-294-541-2417', '[email protected]'),
  40. (4, '2022-10-02', 'Perry Rivers', '1-424-528-8864', '[email protected]'),
  41. (5, '2022-12-17', 'Edan Mcclain', '(502) 732-6779', '[email protected]'),
  42. (6, '2023-11-07', 'Judith Vega', '1-846-721-3113', '[email protected]'),
  43. (7, '2022-10-18', 'Lee Maxwell', '1-431-167-0862', '[email protected]'),
  44. (8, '2023-10-09', 'Kenneth Sullivan', '1-670-944-9195', '[email protected]'),
  45. (9, '2022-08-22', 'Linda Gibbs', '(382) 878-2928', '[email protected]'),
  46. (10, '2023-12-03', 'Sybill Mccarthy', '(749) 765-6633', '[email protected]'),
  47. (11, '2023-10-04', 'Wyatt Molina', '(960) 659-7470', '[email protected]'),
  48. (12, '2024-02-20', 'Tanner Valencia', '1-931-495-2223', '[email protected]'),
  49. (13, '2023-02-26', 'Shana Horne', '(684) 666-1787', '[email protected]'),
  50. (14, '2023-06-24', 'Aidan White', '(635) 555-1553', '[email protected]'),
  51. (15, '2024-03-17', 'Barry Mejia', '(595) 541-5476', '[email protected]'),
  52. (16, '2023-08-06', 'Lavinia Petersen', '(541) 683-2364', '[email protected]'),
  53. (17, '2024-04-21', 'Maite Moran', '1-454-259-0575', '[email protected]'),
  54. (18, '2023-08-31', 'Adrian Lancaster', '(424) 332-8414', '[email protected]'),
  55. (19, '2022-11-13', 'Constance Andrews', '1-267-588-7731', '[email protected]'),
  56. (20, '2023-05-01', 'Byron Fitzgerald', '1-885-886-9975', '[email protected]'),
  57. (21, '2022-11-18', 'Guinevere Rowland', '(537) 218-7301', '[email protected]'),
  58. (22, '2023-08-20', 'Francis Mccray', '1-642-391-4684', '[email protected]'),
  59. (23, '2023-08-25', 'Brandon Warren', '(541) 837-0347', '[email protected]'),
  60. (24, '2023-08-23', 'Whoopi Nixon', '(325) 914-5862', '[email protected]'),
  61. (25, '2023-04-09', 'Rigel Barrett', '1-921-795-7546', '[email protected]'),
  62. (26, '2022-11-30', 'Josephine Hess', '1-571-456-4434', '[email protected]'),
  63. (27, '2023-03-24', 'Judith Lindsey', '(342) 323-8029', '[email protected]'),
  64. (28, '2023-01-16', 'Kaye Flowers', '(826) 265-2891', '[email protected]'),
  65. (29, '2024-01-05', 'Kirsten Warren', '1-744-346-3683', '[email protected]'),
  66. (30, '2022-10-10', 'Reagan West', '(514) 495-7280', '[email protected]'),
  67. (31, '2023-10-07', 'Mohammad Forbes', '1-286-285-6449', '[email protected]'),
  68. (32, '2023-05-30', 'Elvis Davidson', '(322) 453-9479', '[email protected]'),
  69. (33, '2023-08-26', 'Mark Sharp', '1-573-513-8548', '[email protected]'),
  70. (34, '2024-03-28', 'Ralph Mcconnell', '1-518-440-4739', '[email protected]'),
  71. (35, '2023-07-13', 'Tatyana Collier', '1-442-728-3255', '[email protected]'),
  72. (36, '2023-12-02', 'Lesley Mcneil', '(749) 735-6841', '[email protected]'),
  73. (37, '2023-03-06', 'Keith Valencia', '1-127-287-2917', '[email protected]'),
  74. (38, '2022-08-28', 'Dieter Browning', '(631) 831-4373', '[email protected]'),
  75. (39, '2023-02-22', 'Ava Weiss', '1-521-887-1971', '[email protected]'),
  76. (40, '2023-05-28', 'Mannix Edwards', '(413) 951-8282', '[email protected]'),
  77. (41, '2022-11-08', 'Rylee Macias', '1-713-235-2668', '[email protected]'),
  78. (42, '2023-11-27', 'Liberty Richardson', '1-612-655-6844', '[email protected]'),
  79. (43, '2023-10-26', 'Cairo Hoover', '1-988-624-1575', '[email protected]'),
  80. (44, '2023-05-05', 'Giacomo Trevino', '(514) 780-6411', '[email protected]'),
  81. (45, '2023-11-08', 'Jada Talley', '(671) 501-1876', '[email protected]'),
  82. (46, '2023-07-18', 'Emmanuel Barnett', '(412) 855-1134', '[email protected]'),
  83. (47, '2022-12-05', 'Eugenia Farmer', '(661) 476-8221', '[email protected]'),
  84. (48, '2023-10-24', 'Justina Steele', '(348) 542-4396', '[email protected]'),
  85. (49, '2022-12-28', 'Cathleen Garner', '1-841-372-9238', '[email protected]'),
  86. (50, '2023-12-18', 'Candice Stokes', '(812) 517-1254', '[email protected]');
  87.  
  88. --
  89. -- Indexes for dumped tables
  90. --
  91.  
  92. --
  93. -- Indexes for table `members`
  94. --
  95. ALTER TABLE `members`
  96.     ADD PRIMARY KEY (`id`);
  97.  
  98. --
  99. -- AUTO_INCREMENT for dumped tables
  100. --
  101.  
  102. --
  103. -- AUTO_INCREMENT for table `members`
  104. --
  105. ALTER TABLE `members`
  108.    

Creating the Database Connection

The following script is a PHP file known as db-connect.php. This file contains the PHP codes that connect the application to the MySQL Database.

  1. <?php
  2. // DB Host
  3. $host = "localhost";
  4. // DB Username
  5. $username = "root";
  6. // DB Password
  7. $password = "";
  8. // DB Name
  9. $dbname = "dummy_db";
  10.  
  11. // Try to connect into the database
  12. try{
  13.     // Connecting to the database using MySQLi Driver
  14.     $conn = new MySQLi($host, $username, $password, $dbname);
  15. }catch(Exception $e){
  16.     // Return Error Message
  17.     die($e->getMessage());
  18. }

HTML

Here's the HTML file script known as index.php. This file contains the page layout, filter form, and table elements of the web page. The file loads the database connection file to allow the script to retrieve data from the database.

  1. <?php
  2. // Include Database Connection Filescript
  3. include('db-connect.php');
  4. ?>
  5. <?php
  6. // Get Filter From Date if Exist otherwise set it in 3 months ago
  7. $from = $_GET['from'] ?? date("Y-m-d", strtotime(date("Y-m-d").' -3 months'));
  8. // Get Filter To Date if Exist otherwise set it in current date
  9. $to = $_GET['to'] ?? date("Y-m-d");
  10. ?>
  11. <!DOCTYPE html>
  12. <html lang="en">
  14.     <meta charset="UTF-8">
  15.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  16.     <title>Retrieving Data Between 2 Dates From DB</title>
  17.     <link rel="stylesheet" href="style.css">
  18.     <link rel="preconnect" href="https://fonts.googleapis.com">
  19.     <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
  20.     <link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Material+Symbols+Outlined:opsz,wght,FILL,[email protected],100..700,0..1,-50..200" />
  21. </head>
  23.     <div class="container">
  24.         <h1 id="page-title">Retrieving Data Between 2 Dates From DB using PHP and MySQL Database</h1>
  25.         <hr id="title_hr">
  26.         <!-- table Wrapper -->
  27.         <div id="tbl-wrapper">
  28.             <!-- Filter Form -->
  29.             <form action="">
  30.                 <!-- Date From -->
  31.                 <div class="form-group">
  32.                     <label for="from">Date From</label>
  33.                     <input type="date" class="input-field" required="required" name="from" id="from" value="<?= $from ?>" max="<?= $to ?>">
  34.                 </div>
  35.                 <!-- Date From -->
  36.                 <!-- Date To -->
  37.                 <div class="form-group">
  38.                     <label for="to">Date To</label>
  39.                     <input type="date" class="input-field" required="required" name="to" id="to" value="<?= $to ?>" min="<?= $from ?>">
  40.                 </div>
  41.                 <!-- Date To -->
  42.                 <!-- Filter Button -->
  43.                 <div class="form-group">
  44.                     <button id="btn-filter"><span class="material-symbols-outlined">filter_list</span> Filter</button>
  45.                 </div>
  46.                 <!-- Filter Button -->
  47.             </form>
  48.             <!-- Filter Form -->
  49.             <!-- Table -->
  50.             <table id="data-tbl">
  51.                 <thead>
  52.                     <tr>
  53.                         <th>Date Created</th>
  54.                         <th>Name</th>
  55.                         <th>Email</th>
  56.                         <th>Phone #</th>
  57.                     </tr>
  58.                 </thead>
  59.                 <tbody>
  60.                     <?php
  61.                    // SQL Statement
  62.                    $sql = "SELECT * FROM `members` where date(`created_date`) BETWEEN '{$from}' and '{$to}' ";
  63.                    // Execute Query
  64.                    $qry = $conn->query($sql);
  65.                     if($qry->num_rows > 0):
  66.                         // If data row count is greater that 0, display row's data
  67.                         foreach($qry->fetch_all(MYSQLI_ASSOC) as $row):
  68.                     ?>
  69.                     <tr>
  70.                         <td><?= date("M d, y", strtotime($row['created_date'])) ?></td>
  71.                         <td><?= ucwords($row['name']) ?></td>
  72.                         <td><?= $row['email'] ?></td>
  73.                         <td><?= $row['phone'] ?></td>
  74.                     </tr>
  75.                     <?php endforeach; ?>
  76.                     <?php else: ?>
  77.                         <!-- If data rows count is less than equal 0, display a message-->
  78.                         <tr>
  79.                             <td colspan="4" class="text-center">No data found.</td>
  80.                         </tr>
  81.                     <?php endif; ?>
  82.                 </tbody>
  83.             </table>
  84.             <!-- Table -->
  85.         </div>
  86.         <!-- table Wrapper -->
  87.     </div>
  88.     <?php
  89.    if($conn)
  90.    $conn->close();
  91.     ?>
  92.     <script src="script.js"></script>
  93. </body>
  94. </html>

The script above contains the SQL statement that filters the fetched data from the database. By default date, the range value is 3 months ago as the date range start and ends with the current date. If the filter form has been updated and submitted, the page will reload and filter the data based on the given dates.

Stylesheet

The application contains the following CSS file known as style.css. It contains the stylesheet codes of the page layout and other elements' designs.

  1. @import url('https://fonts.googleapis.com/css2?family=Dongle:wght@300;400;700&family=Roboto+Mono:ital,wght@0,100;0,200;0,300;0,400;0,500;0,600;1,100;1,200;1,300;1,400;1,500;1,600&display=swap" rel="stylesheet');
  2. *{
  3.     margin: 0;
  4.     padding: 0;
  5.     box-sizing: border-box;
  6.     font-family: 'Dongle', sans-serif;
  7.     font-family: 'Roboto Mono', monospace;
  8. }
  9. ::selection{
  10.     color: #fff;
  11.     background: #4db2ec;
  12. }
  13. body{
  14.     display: flex;
  15.     align-items: center;
  16.     justify-content: center;
  17.     min-height: 100vh;
  18.     background: #4facfe;
  19.     background-image: linear-gradient(to right, #4facfe 0%, #00f2fe 100%);
  20.     padding: 2em 0;
  21. }
  22. #page-title{
  23.     color: #fff;
  24.     text-align: center;
  25.     font-weight: 500;
  26.     text-shadow: 0px 0px 15px #0000003a;
  27. }
  28. #title_hr{
  29.     width:60px;
  30.     border: 2px solid #ffffff;
  31.     margin: .35em auto;
  32. }
  33. @media (min-width: 780px){
  34.     #page-title{
  35.     width: 780px;
  36.     }
  37. }
  38. /* Table Wrapper */
  39. div#tbl-wrapper {
  40.     width: 760px;
  41.     background: #fff;
  42.     padding: 1em 1.35em;
  43.     border: 1px solid #c9c9c9;
  44.     box-shadow: 1px 1px 10px #0000003b;
  45. }
  46. @media (min-width: 760px){
  47.     #tbl-wrapper{
  48.     width: 955;
  49.     }
  50. }
  51. /* Filter Form */
  52. #tbl-wrapper>form {
  53.     display: flex;
  54.     flex-wrap: wrap;
  55.     align-items: end;
  56.     justify-content: space-evenly;
  57.     padding: 1em 0;
  58. }
  59. /* Form Group */
  60. #tbl-wrapper>form .form-group {
  61.     width: calc(100% / 3);
  62.     display: flex;
  63.     flex-wrap: wrap;
  64.     padding: .5em .25em;
  65.     justify-content: center;
  66. }
  67. /* Form Group Label */
  68. #tbl-wrapper>form .form-group>label {
  69.     display: block;
  70.     width: 100%;
  71.     font-weight: 500;
  72.     color: #545454;
  73. }
  74.  
  75. /* Form Group Input Field */
  76. #tbl-wrapper>form .form-group>.input-field {
  77.     display: block;
  78.     width: 100%;
  79.     padding: 0.35em 0.5em;
  80.     border: 1px solid #d9d8d8;
  81.     outline: none;
  82. }
  83. /* Form Group Label on focus*/
  84. #tbl-wrapper>form .form-group>.input-field:focus {
  85.     border: 1px solid #0051ff69;
  86. }
  87.  
  88. /* Filter Button*/
  89. button#btn-filter {
  90.     background: #3bb7ff;
  91.     border: unset;
  92.     padding: 0.35em 0.5em;
  93.     color: #fff;
  94.     font-weight: 500;
  95.     display: flex;
  96.     align-items: center;
  97.     justify-content: center;
  98.     min-width: 75%;
  99. }
  100.  
  101. /* Table */
  102. table#data-tbl {
  103.     width: 100%;
  104.     border-collapse: collapse;
  105. }
  106. /* Table Header*/
  107. table#data-tbl thead {
  108.     background: #4caefe;
  109.     color: #fff;
  110. }
  111. /* Table cells*/
  112. table#data-tbl td, table#data-tbl th {
  113.     border: 1px solid #efefef;
  114.     padding: 0.15em 0.35em;
  115. }
  116. /* Table Header cell*/
  117. table#data-tbl th {
  118.     text-align: center;
  119. }
  120.  
  121. /* Table row no data*/
  122. table#data-tbl td[colspan="4"] {
  123.     text-align: center;
  124.     color:#9f9f9f;
  125.     font-style:italic;
  126. }

JavaScript

The following script is the JavaScript file known as script.js. The file contains the codes the updates the date range input fields attribute for limiting the maximum and minimum dates that can be selected.

  1. // Date Range From
  2. const from = document.getElementById('from')
  3. // Date Range To
  4. const to = document.getElementById('to')
  5.  
  6. // When Date From Input Field value has change, update Date To Input Field minimum date
  7. from.addEventListener('change', ()=>{
  8.     to.setAttribute('min', from.value)
  9. })
  10.  
  11. // When Date To Input Field value has change, update Date From Input Field maximum date
  12. to.addEventListener('change', ()=>{
  13.     from.setAttribute('max', to.value)
  14. })

Snapshots

Here are some snapshots of the overall results of the scripts I have provided above.

Page Interface

Retrieving Data Between 2 Dates From DB using PHP and MySQL Database

Display Wrapper

Retrieving Data Between 2 Dates From DB using PHP and MySQL Database

Filter Form

Retrieving Data Between 2 Dates From DB using PHP and MySQL Database

Table

Retrieving Data Between 2 Dates From DB using PHP and MySQL Database

There you go! I have also provided the complete source code zip file on this website and it is free to download. The download button is located below this tutorial's content. Feel free to download the source code the way you wanted to enhance your programming capabilities.

DEMO VIDEO

That's it! I hope this Retrieving Data Between 2 Dates From Database using PHP and MySQL DB Tutorial will help you with what you are looking for and you'll find it useful for your current and future PHP Projects.

Happy Coding =)

Tags
php
php tutorial
PHP and MYSQL Database
MySQL Database Tutorial
Date Range
Retrieving data between 2 dates

Add new comment

About text formats
  • 557 views