Date range search with jQuery Datepicker using Ajax, PHP & MySQL

by Clue Mediator · January 13, 2021

In this article, we’ll discuss the date range search with jQuery Datepicker using Ajax, PHP and MySQL.

Here, we will see how to search data from a MySQL database based on the date between two given ranges. We are using jQuery Datepicker to select dates for filter the data.

You may also like following articles related to AJAX.

Demo Example

Output - Date range search with jQuery Datepicker using Ajax, PHP & MySQL - Clue Mediator — Output – Date range search with jQuery Datepicker using Ajax, PHP & MySQL – Clue Mediator

Steps to implement Date Range Search with jQuery DatePicker

Create table in database
Database connection
Create HTML page
Add Ajax call
Create PHP action file
Output

File Structure

date-range-search-php
- action.php
- db_config.php
- index.php

1. Create table in database

First, we will create a table named orders in the database. Run the following script to create a table with dummy records in the database.

# Create orders table
CREATE TABLE `orders` (
order_number int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
customer_name varchar(255) NOT NULL,
purchased_items varchar(255) NOT NULL,
price double(12,2) NOT NULL,
purchased_date date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Inserting data in the order table
INSERT INTO `orders` (`order_number`, `customer_name`, `purchased_items`, `price`, `purchased_date`) VALUES
(1, 'John Derosa', 'iPhone', 745.00, '2020-11-05'),
(2, 'Oscar Thomas', 'CCTV Camera', 956.00, '2020-11-12'),
(3, 'Karen Victory', 'Glass Block', 250.00, '2020-11-16'),
(4, 'David McGee', 'Shuttering Plywood', 1450.00, '2020-11-23'),
(5, 'Ronald Gary', 'CCTV Camera', 765.00, '2020-12-10'),
(6, 'Jessica Hunter', 'Casing Pipes', 600.00, '2020-12-14'),
(7, 'Helena Sorensen', 'Apple Watch', 590.00, '2020-12-23'),
(8, 'Lori Hunter', 'Glass PVC Rubber', 1700.00, '2020-12-25'),
(9, 'Diane Nathan', 'Microwave', 200.00, '2021-01-01'),
(10, 'Oscar Jury', 'Macbook', 1600.00, '2021-01-08');

# Create orders table

CREATE TABLE `orders` (

order_number int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

customer_name varchar(255) NOT NULL,

purchased_items varchar(255) NOT NULL,

price double(12,2) NOT NULL,

purchased_date date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Inserting data in the order table

INSERT INTO `orders` (`order_number`, `customer_name`, `purchased_items`, `price`, `purchased_date`) VALUES

(1, 'John Derosa', 'iPhone', 745.00, '2020-11-05'),

(2, 'Oscar Thomas', 'CCTV Camera', 956.00, '2020-11-12'),

(3, 'Karen Victory', 'Glass Block', 250.00, '2020-11-16'),

(4, 'David McGee', 'Shuttering Plywood', 1450.00, '2020-11-23'),

(5, 'Ronald Gary', 'CCTV Camera', 765.00, '2020-12-10'),

(6, 'Jessica Hunter', 'Casing Pipes', 600.00, '2020-12-14'),

(7, 'Helena Sorensen', 'Apple Watch', 590.00, '2020-12-23'),

(8, 'Lori Hunter', 'Glass PVC Rubber', 1700.00, '2020-12-25'),

(9, 'Diane Nathan', 'Microwave', 200.00, '2021-01-01'),

(10, 'Oscar Jury', 'Macbook', 1600.00, '2021-01-08');

2. Database connection

We have to create a db_config.php file and add the following code to connect the database.

db_config.php

<?php
	$dbhost = "localhost"; // host name
	$dbuser = "root";  // username
	$dbpass = "";  // password
	$db = "demo";  // database name
	$con = mysqli_connect($dbhost, $dbuser, $dbpass , $db) or die($con);
?>

<?php

$dbhost = "localhost"; // host name

$dbuser = "root"; // username

$dbpass = ""; // password

$db = "demo"; // database name

$con = mysqli_connect($dbhost, $dbuser, $dbpass , $db) or die($con);

3. Create HTML page

Now, we will create a file named index.php where two input fields for the date are shown.

When the search button is clicked, the Ajax request is sent to the action.php file. The PHP script will filter the data based on the start date and end date using MySQL query with between cause. So the filtered data will be shown on the web page without refreshing the page using Ajax.

index.php

<?php
// include database connection file
include('db_config.php');

$query = "SELECT * FROM orders ORDER BY order_number desc";
$result = mysqli_query($con, $query);
?>

<html>

<head>
  <title>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - Clue Mediator</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
</head>

<body>
  <div class="container">
    <h4>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - <a href="https://www.cluemediator.com/" target="_blank" rel="noopener noreferrer">Clue Mediator</a></h4>
    </br>
    <div class="row">
      <div class="col-md-2">
        <input type="text" name="from_date" id="from_date" class="form-control dateFilter" placeholder="From Date" />
      </div>
      <div class="col-md-2">
        <input type="text" name="to_date" id="to_date" class="form-control dateFilter" placeholder="To Date" />
      </div>
      <div class="col-md-2">
        <input type="button" name="search" id="btn_search" value="Search" class="btn btn-primary" />
      </div>
    </div>
    </br>
    <div class="row">
      <div class="col-md-8">
        <div id="purchase_order">
          <table class="table table-bordered">
            <tr>
              <th width="5%">Order Number</th>
              <th width="30%">Customer Name</th>
              <th width="40%">Item</th>
              <th width="15%">Price</th>
              <th width="10%">Purchased Date</th>
            </tr>
            <?php
            while($row = mysqli_fetch_array($result))
            {
            ?>
              <tr>
                  <td><?php echo $row["order_number"]; ?></td>
                  <td><?php echo $row["customer_name"]; ?></td>
                  <td><?php echo $row["purchased_items"]; ?></td>
                  <td>$ <?php echo $row["price"]; ?></td>
                  <td><?php echo $row["purchased_date"]; ?></td>
              </tr>
            <?php
            }
            ?>
          </table>
        </div>
      </div>
    </div>
  </div>
</body>
</html>

<?php

// include database connection file

include('db_config.php');

$query = "SELECT * FROM orders ORDER BY order_number desc";

$result = mysqli_query($con, $query);

<html>

<head>

<title>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - Clue Mediator</title>

</head>

<body>

<h4>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - <a href="https://www.cluemediator.com/" target="_blank" rel="noopener noreferrer">Clue Mediator</a></h4>

</br>

</div>

</div>

</div>

</br>

<tr>

<th width="5%">Order Number</th>

<th width="30%">Customer Name</th>

<th width="15%">Price</th>

<th width="10%">Purchased Date</th>

</tr>

<?php

while($row = mysqli_fetch_array($result))

{

<tr>

</tr>

<?php

}

</table>

</div>

</body>

</html>

4. Add Ajax call

Let’s add the following code in the index.php file that sends from_date and to_date to the server-side script via Ajax request when the search button is clicked.

Get the response from the action.php file in table format and display the HTML data to the div element.

<script>
  $(document).ready(function () {

    $('.dateFilter').datepicker({
      dateFormat: "yy-mm-dd"
    });

    $('#btn_search').click(function () {
      var from_date = $('#from_date').val();
      var to_date = $('#to_date').val();
      if (from_date != '' && to_date != '') {
        $.ajax({
          url: "action.php",
          method: "POST",
          data: { from_date: from_date, to_date: to_date },
          success: function (data) {
            $('#purchase_order').html(data);
          }
        });
      }
      else {
        alert("Please Select the Date");
      }
    });
  });
</script>

$(document).ready(function () {

$('.dateFilter').datepicker({

dateFormat: "yy-mm-dd"

});

$('#btn_search').click(function () {

var from_date = $('#from_date').val();

var to_date = $('#to_date').val();

if (from_date != '' && to_date != '') {

$.ajax({

url: "action.php",

method: "POST",

data: { from_date: from_date, to_date: to_date },

success: function (data) {

$('#purchase_order').html(data);

}

});

}

else {

alert("Please Select the Date");

}

});

</script>

5. Create PHP action file

At last, we will create a file named action.php. The Ajax request is sent to this PHP file and then filter the data from the database based on the request id (from_date and to_date).

action.php

<?php
// include database connection file
include('db_config.php');

if(isset($_POST["from_date"], $_POST["to_date"])) {
    $orderData = "";
    $query = "SELECT * FROM orders WHERE purchased_date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."'";
    $result = mysqli_query($con, $query);

    $orderData .='
    <table class="table table-bordered">
    <tr>
    <th width="5%">Order Number</th>
    <th width="30%">Customer Name</th>
    <th width="40%">Item</th>
    <th width="15%">Price</th>
    <th width="10%">Purchased Date</th>
    </tr>';

    if(mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_array($result))
        {
            $orderData .='
            <tr>
            <td>'.$row["order_number"].'</td>
            <td>'.$row["customer_name"].'</td>
            <td>'.$row["purchased_items"].'</td>
            <td>'.$row["price"].'</td>
            <td>'.$row["purchased_date"].'</td>
            </tr>';
        }
    }
    else
    {
        $orderData .= '
        <tr>
        <td colspan="5">No Order Found</td>
        </tr>';
    }
    $orderData .= '</table>';
    echo $orderData;
}
?>

<?php

// include database connection file

include('db_config.php');

if(isset($_POST["from_date"], $_POST["to_date"])) {

$orderData = "";

$query = "SELECT * FROM orders WHERE purchased_date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."'";

$result = mysqli_query($con, $query);

$orderData .='

<tr>

<th width="5%">Order Number</th>

<th width="30%">Customer Name</th>

<th width="15%">Price</th>

<th width="10%">Purchased Date</th>

</tr>';

if(mysqli_num_rows($result) > 0)

{

while($row = mysqli_fetch_array($result))

{

$orderData .='

<tr>

<td>'.$row["order_number"].'</td>

<td>'.$row["customer_name"].'</td>

<td>'.$row["purchased_items"].'</td>

<td>'.$row["price"].'</td>

<td>'.$row["purchased_date"].'</td>

</tr>';

}

else

{

$orderData .= '

<tr>

<td colspan="5">No Order Found</td>

</tr>';

}

$orderData .= '</table>';

echo $orderData;

}

6. Output

Run the project and check the output in the browser.

That’s it for today.
Thank you for reading. Happy Coding..!! 🙂

Demo & Source Code

Github Repository

If you found value in this article,
you can support us by buying me a coffee! ☕

Date range search with jQuery Datepicker using Ajax, PHP & MySQL

Demo Example

Steps to implement Date Range Search with jQuery DatePicker

File Structure

1. Create table in database

2. Database connection

3. Create HTML page

4. Add Ajax call

5. Create PHP action file

6. Output

Demo & Source Code

You may also like...

Leave a Reply Cancel reply

Search your query

JOIN OUR NEWSLETTER

Recent Posts

Join us

Date range search with jQuery Datepicker using Ajax, PHP & MySQL

Demo Example

Steps to implement Date Range Search with jQuery DatePicker

File Structure

1. Create table in database

2. Database connection

3. Create HTML page

4. Add Ajax call

5. Create PHP action file

6. Output

Demo & Source Code

You may also like...

Add or Subtract days, month and year to a date using PHP

How to generate random Boolean values in PHP

File Transfers with PHP FTP Functions

Insert an array into a MySQL database using PHP

Dynamic dependent select box using jQuery, Ajax, and PHP

Add or remove input fields dynamically in PHP using jQuery

Leave a Reply Cancel reply

Search your query

JOIN OUR NEWSLETTER

Recent Posts

Tags

Join us