Simple Date Range Search Using PHP/Ajax

Submitted by razormist on Tuesday, March 7, 2017 - 17:34.
In this tutorial we will try to create a search function based on the two date range using Ajax. Ajax is a client-side script that communicates to server without the need for a complete page refresh. By using ajax It let's your website more interactive to the user. Most of the function in facebook uses ajax, that's why it is very convenient to use. Now that we know how about ajax, let's start coding. Creating the database In creating a database we just need a program to make a database worked (wamp, xamp, etc..). After opening your web server create a database and name it "db_search". Then click SQL and copy/paste the code below and run the sql query
  1. -- phpMyAdmin SQL Dump
  2. -- version 4.5.1
  3. -- http://www.phpmyadmin.net
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: Mar 07, 2017 at 06:12 AM
  7. -- Server version: 10.1.16-MariaDB
  8. -- PHP Version: 5.6.24
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. SET time_zone = "+00:00";
  12.  
  13.  
  14. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  15. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  16. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  17. /*!40101 SET NAMES utf8mb4 */;
  18.  
  19. --
  20. -- Database: `db_search`
  21. --
  22.  
  23. -- --------------------------------------------------------
  24.  
  25. --
  26. -- Table structure for table `book`
  27. --
  28.  
  29. CREATE TABLE `book` (
  30.   `book_id` int(11) NOT NULL,
  31.   `ISBN` varchar(20) NOT NULL,
  32.   `title` varchar(100) NOT NULL,
  33.   `author` varchar(50) NOT NULL,
  34.   `date_published` date NOT NULL
  36.  
  37. --
  38. -- Dumping data for table `book`
  39. --
  40.  
  41. INSERT INTO `book` (`book_id`, `ISBN`, `title`, `author`, `date_published`) VALUES
  42. (1, '978-1-891830-71-6', 'AEIOU or Any Easy Intimacy ', 'Jeffrey Brown', '2017-03-08'),
  43. (2, '978-1-60309-2395', 'American Elf 1999', 'James Kochalka', '2017-03-17'),
  44. (3, '978-1-891830-37-2', 'The Barefoot Serpent (softcover)', ' Scott Morse', '2017-04-04'),
  45. (4, '978-1-891830-40-2', 'Beach Safari', 'Mawil', '2017-03-17'),
  46. (5, '978-1-891830-56-3', 'Bighead', 'Jeffrey Brown', '2017-04-01');
  47.  
  48. --
  49. -- Indexes for dumped tables
  50. --
  51.  
  52. --
  53. -- Indexes for table `book`
  54. --
  55. ALTER TABLE `book`
  56.   ADD PRIMARY KEY (`book_id`);
  57.  
  58. --
  59. -- AUTO_INCREMENT for dumped tables
  60. --
  61.  
  62. --
  63. -- AUTO_INCREMENT for table `book`
  64. --
  65. ALTER TABLE `book`
  67. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  68. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  69. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Creating the form To create a form open any kind of text editor that your computer already installed(notepad, notepad++, etc..) or if there's not I highly recommended to install a text editor. After that copy/paste the given code below and then name it "index.php".
  1. <!DOCTYPE html>
  2. <html lang = "en">
  3.         <head>
  4.                 <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css"/>
  5.                 <link rel = "stylesheet" type = "text/css" href = "css/jquery-ui.css"/>
  6.                 <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1"/>
  7.         </head>
  8. <body>
  9.         <nav class = "navbar navbar-default">
  10.                 <div class = "container-fluid">
  11.                         <a href = "https://sourcecodester.com" class = "navbar-brand">Sourcecodester</a>
  12.                 </div>
  13.         </nav>
  14.         <div class = "row">
  15.                 <div class = "col-md-3"></div>
  16.                 <div class = "col-md-6 well">
  17.                         <h3 class = "text-primary">Simple Date Range Search Using PHP & Ajax</h3>
  18.                         <hr style = "border-top:1px dotted #000;"/>
  19.                         <div class = "form-inline">
  20.                                 <label>Date:</label>
  21.                                 <input type = "text" class = "form-control" placeholder = "Start"  id = "date1"/>
  22.                                 <label>To</label>
  23.                                 <input type = "text" class = "form-control" placeholder = "End"  id = "date2"/>
  24.                                 <button type = "button" class = "btn btn-primary" id = "btn_search"><span class = "glyphicon glyphicon-search"></span></button> <button type = "button" id = "reset" class = "btn btn-success"><span class = "glyphicon glyphicon-refresh"><span></button>
  25.                         </div>
  26.                         <br /><br />
  27.                         <div class = "table-responsive">       
  28.                                 <table class = "table table-bordered alert-warning">
  29.                                         <thead>
  30.                                                 <tr>
  31.                                                         <th style = "width:25%;">ISBN</th>
  32.                                                         <th style = "width:30%;">Title</th>
  33.                                                         <th>Author</th>
  34.                                                         <th style = "width:20%;">Date Published</th>
  35.                                                 </tr>
  36.                                         </thead>
  37.                                         <tbody id = "load_data">
  38.                                                 <?php
  39.                                                         $conn = new mysqli("localhost", "root", "", "db_search");
  40.                                                         if(!$conn){
  41.                                                                 die("Fatal Error: Connection Error!");
  42.                                                         }
  43.                                                        
  44.                                                         $q_book = $conn->query("SELECT * FROM `book` ORDER BY `title` ASC") or die(mysqli_error());
  45.                                                         while($f_book = $q_book->fetch_array()){
  46.                                                 ?>
  47.                                                 <tr>
  48.                                                         <td><?php echo $f_book['ISBN']?></td>
  49.                                                         <td><?php echo $f_book['title']?></td>
  50.                                                         <td><?php echo $f_book['author']?></td>
  51.                                                         <td><?php echo date("m/d/Y", strtotime($f_book['date_published']))?></td>
  52.                                                 </tr>
  53.                                                 <?php
  54.                                                         }
  55.                                                 ?>
  56.                                         </tbody>
  57.                                 </table>
  58.                         </div> 
  59.                 </div>
  60.         </div>
  61. </body>
  62. <script src = "js/jquery-3.1.1.js"></script>
  63. <script src = "js/jquery-ui.js"></script>
  64. <script src = "js/ajax.js"></script>
  65. </html>
Creating a ajax response query We will create a query that will call back the request of an ajax from the server to display the exact value of an input. To do that copy/paste the given code below then name it "get_data.php".
  1. <?php
  2. $date1 = date("Y-m-d", strtotime($_POST['date1']));
  3. $date2 = date("Y-m-d", strtotime($_POST['date2']));
  4. $conn = new mysqli("localhost", "root", "", "db_search");
  5. if(!$conn){
  6.         die("Fatal Error: Connection Error!");
  7. }
  8.        
  9. $q_book = $conn->query("SELECT * FROM `book` WHERE `date_published` BETWEEN '$date1' AND '$date2' ORDER BY `title` ASC") or die(mysqli_error());
  10. $v_book = $q_book->num_rows;
  11. if($v_book > 0){
  12.         while($f_book = $q_book->fetch_array()){
  13.         ?>
  14.         <tr>
  15.                 <td><?php echo $f_book['ISBN']?></td>
  16.                 <td><?php echo $f_book['title']?></td>
  17.                 <td><?php echo $f_book['author']?></td>
  18.                 <td><?php echo date("m/d/Y", strtotime($f_book['date_published']))?></td>
  19.         </tr>
  20.         <?php
  21.         }
  22. }else{
  23.                 echo '
  24.                 <tr>
  25.                         <td colspan = "4"><center>Record Not Found</center></td>
  26.                 </tr>
  27.                 ';
  28. }
  29.         ?>
Creating the ajax script We will now then create the ajax that make the simple application fully works. By adding the ajax script it will make an application more interactive to the user because it prevent the web page by refreshing that makes it looks real time updater. To do that copy/paste the given code below and name it "ajax.js"
  1. $(document).ready(function(){
  2.         $('#date1').datepicker();
  3.         $('#date2').datepicker();
  4.         $('#btn_search').on('click', function(){       
  5.                 if($('#date1').val() == "" || $('#date2').val() == ""){
  6.                         alert("Please enter something on the text field");
  7.                 }else{
  8.                         $date1 = $('#date1').val();
  9.                         $date2 = $('#date2').val();
  10.                         $('#load_data').empty();
  11.                         $loader = $('<tr ><td colspan = "4"><center>Searching....</center></td></tr>');
  12.                         $loader.appendTo('#load_data');
  13.                         setTimeout(function(){
  14.                                 $loader.remove();
  15.                                 $.ajax({
  16.                                         url: 'get_data.php',
  17.                                         type: 'POST',
  18.                                         data: {
  19.                                                 date1: $date1,
  20.                                                 date2: $date2
  21.                                         },
  22.                                         success: function(res){
  23.                                                 $('#load_data').html(res);
  24.                                         }
  25.                                 });
  26.                         }, 3000);
  27.                 }      
  28.         });
  29.        
  30.         $('#reset').on('click', function(){
  31.                 location.reload();
  32.         });
  33. });
There you have it we created a simple date range search by using ajax/PHP. I hope that you learn something on this tutorial. For more updates and tutorials just kindly visit this site. Enjoy Coding!!
Tags
AJAX
php

Add new comment

About text formats
  • 1655 views