Load Last N Days Data into Datatables using PHP Ajax

In this blog we have made discussion on topic like How to load last dynamic days data into Datatables by using PHP script with Ajax Jquery. Most of the programmers are used Jquery Datatables plugin for display data in tabular format on web page. Because it is light weight and it provide lots of feature like search data, sort table column, pagination and many more other features. So for example now we want to load last 30 days data from mysql table by using PHP script and display under Jquery Datatables plugin. Because this type of feature is not found under this plugin. For use functionality of this feature we want to write some extra code at front end and back end.

Here we have use HTML select box for list number of days. So user can select days for get last particular days data. When user select days from select box then we will first destroy Datatable by using destroy() method and then after we have again called Datatables Ajax request with value of days select from drop down list box. So here we have send value of selected days to server via Ajax request. On PHP server script we have append query with condition like fetch data between both date. In condition first we want to get start date, so for get start date we have minus selected days from current date. So this ways we have get start date and for end date we can get from CURDATE() mysql function. So this ways we can get start date and end date for fetch data between this two date. So this ways we have append query for fetch last particular data from Mysql table and it will load data without refresh of web page.

If we have use date datatype in table then this feature will helpful to fetch last N days data from table by using PHP code with Ajax JQuery. By using this functionality with Jquery Datatables plugin then we can easily get last particular days data and load into table. This feature will increase the value of your code and add extra functionality into Datatables features. So I hope you have understand this topic which we have describe in this post. Below this you can find source code of this post.

View Demo

Source Code

index.php


<html>
 <head>
  <title>Display Last N Days Data into Datatables using PHP Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  
 </head>
 <body>
  <div class="container">
   <h2 align="center">Display Last N Days Data into Datatables using PHP Ajax</h2>
   <br />
   <div class="row">
    <div class="col-md-2">
     <select name="days_filter" id="days_filter" class="form-control">
      <option value="">Select Days</option>
      <option value="180">In 180 Days</option>
      <option value="90">In 90 Days</option>
      <option value="60">In 60 Days</option>
                     <option value="30">In 30 Days</option>
     </select>
    </div>
    <div style="clear:both"></div>
    <br />
    <div class="table-responsive">
     <table id="order_data" class="table table-bordered table-striped">
      <thead>
       <tr>
        <th>Customer Name</th>
        <th>Product Name</th>
        <th>Order Value</th>
        <th>Order Date</th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
  </div>
 </body>
</html>



<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 
 load_data();

 function load_data(is_days)
 {
  var dataTable = $('#order_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"fetch.php",
    type:"POST",
    data:{is_days:is_days}
   }
  });
 }

 $(document).on('change', '#days_filter', function(){
  var no_of_days = $(this).val();
  $('#order_data').DataTable().destroy();
  if(no_of_days != '')
  {
   load_data(no_of_days);
  }
  else
  {
   load_data();
  }
 });
 
});
</script>

fetch.php


<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$column = array("order_customer_name", "order_item", "order_value", "order_date");
$query = "
 SELECT * FROM tbl_order WHERE 
";

if(isset($_POST["is_days"]))
{
 $query .= "order_date BETWEEN CURDATE() - INTERVAL ".$_POST["is_days"]." DAY AND CURDATE() AND ";
}

if(isset($_POST["search"]["value"]))
{
 $query .= '(order_customer_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_item LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_value LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_date LIKE "%'.$_POST["search"]["value"].'%") ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY order_id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["order_customer_name"];
 $sub_array[] = $row["order_item"];
 $sub_array[] = $row["order_value"];
 $sub_array[] = $row["order_date"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM tbl_order";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);



?>


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_order`
--

CREATE TABLE IF NOT EXISTS `tbl_order` (
  `order_id` int(11) NOT NULL,
  `order_customer_name` varchar(255) NOT NULL,
  `order_item` varchar(255) NOT NULL,
  `order_value` double(12,2) NOT NULL,
  `order_date` date NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_order`
--

INSERT INTO `tbl_order` (`order_id`, `order_customer_name`, `order_item`, `order_value`, `order_date`) VALUES
(1, 'David E. Gary', 'Shuttering Plywood', 1500.00, '2017-07-14'),
(2, 'Eddie M. Douglas', 'Aluminium Heavy Windows', 2000.00, '2017-06-08'),
(3, 'Oscar D. Scoggins', 'Plaster Of Paris', 150.00, '2017-05-29'),
(4, 'Clara C. Kulik', 'Spin Driller Machine', 350.00, '2017-06-30'),
(5, 'Christopher M. Victory', 'Shopping Trolley', 100.00, '2017-08-01'),
(6, 'Jessica G. Fischer', 'CCTV Camera', 800.00, '2017-04-02'),
(7, 'Roger R. White', 'Truck Tires', 2000.00, '2017-03-28'),
(8, 'Susan C. Richardson', 'Glass Block', 200.00, '2017-02-04'),
(9, 'David C. Jury', 'Casing Pipes', 500.00, '2017-07-27'),
(10, 'Lori C. Skinner', 'Glass PVC Rubber', 1800.00, '2017-05-30'),
(11, 'Shawn S. Derosa', 'Sony HTXT1 2.1-Channel TV', 180.00, '2017-06-03'),
(12, 'Karen A. McGee', 'Over-the-Ear Stereo Headphones ', 25.00, '2017-05-01'),
(13, 'Kristine B. McGraw', 'Tristar 10" Round Copper Chef Pan with Glass Lid', 20.00, '2017-07-30'),
(14, 'Gary M. Porter', 'ROBO 3D R1 Plus 3D Printer', 600.00, '2017-05-02'),
(15, 'Sarah D. Hunter', 'Westinghouse Select Kitchen Appliances', 35.00, '2017-06-29'),
(16, 'Diane J. Thomas', 'SanDisk Ultra 32GB microSDHC', 12.00, '2017-04-05'),
(17, 'Helena J. Quillen', 'TaoTronics Dimmable Outdoor String Lights', 16.00, '2017-05-04'),
(18, 'Arlette G. Nathan', 'TaoTronics Bluetooth in-Ear Headphones', 25.00, '2017-06-03'),
(19, 'Ronald S. Vallejo', 'Scotchgard Fabric Protector, 10-Ounce, 2-Pack', 20.00, '2017-05-03'),
(20, 'Felicia L. Sorensen', 'Anker 24W Dual USB Wall Charger with Foldable Plug', 12.00, '2017-07-04');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_order`
--
ALTER TABLE `tbl_order`
  ADD PRIMARY KEY (`order_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_order`
--
ALTER TABLE `tbl_order`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=21;

2 comments:

Bokele Franck22 May 2018 at 03:44
Load Last N Days Data into Datatables using PHP Ajax with codeIgniter
Unknown6 October 2018 at 14:05
Amazing lesson as any lesson made by You! Thank You endlessly for the great job, ideas, creative projects in general!
I want to ask You, is there possibility to get similar project in PHP7 version (php pdo)? :/
Thank You in advance!
Sincerely,
Irana
irana.miriyeva@gmail.com

Webslesson

PHP, Node.js, React.js, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Saturday 5 August 2017

Load Last N Days Data into Datatables using PHP Ajax

Source Code

index.php

fetch.php

2 comments:

Popular Posts