Clue Mediator

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

📅January 13, 2021
🗁PHP

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

  1. Create table in database
  2. Database connection
  3. Create HTML page
  4. Add Ajax call
  5. Create PHP action file
  6. 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');

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);
?-->

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');<p-->

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




  <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>



  <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">
            <tbody><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
            }
            ?-->
          </tbody></table>
        </div>
      </div>
    </div>
  </div>

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>

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');<p-->

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 .='
    ';

    if(mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_array($result))
        {
            $orderData .='
            ';
        }
    }
    else
    {
        $orderData .= '
        ';
    }
    $orderData .= '<table class="table table-bordered">
    <tbody><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><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><tr>
        <td colspan="5">No Order Found</td>
        </tr></tbody></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

AjaxDateTimejQueryMySQL

Categories

Tags

React ExampleReact HooksReact PackageArrayStringReact AdvancedDateTimeAPISetupImagejQueryFile or DirectoryTableReact BeginnerPickerRegular ExpressionURLErrorDropdownAG Grid