PHP
JavaScript
Angular AngularJS jQuery Ajax
Linux
.htaccess

February 3, 2017

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL

6555

In this tutorial, we are going to see date range search with jQuery datepicker using Ajax, PHP & MySQL. Search MySQL data between two date range without page refresh by using jQuery date picker. Now we will search data from MySQL database base between two given date range and when we will click on filter button then it will send request via ajax method and PHP is helpful to search data from database based on two given dates then the request get back in same ajax method so that we can show filtered data on web page without page refresh.

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | PART – 1

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | PART – 2

Database

CREATE TABLE `orders` (
  `order_number` int(11) NOT NULL,
  `customer_name` varchar(100) NOT NULL,
  `purchased_items` varchar(100) NOT NULL,
  `purchased_date` date NOT NULL,
  `price` double(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `orders` (`order_number`, `customer_name`, `purchased_items`, `purchased_date`, `price`) VALUES
(1, 'MOhan raj', 'iPhone', '2016-11-23', 649.00),
(2, 'Siva', 'iMac', '2016-11-24', 1999.05),
(3, 'Harish', 'iPhone 5s', '2016-11-24', 299.09),
(4, 'Raja', 'Macbook Pro', '2016-11-26', 1799.50),
(5, 'Praveen', 'iPad Air 2', '2016-11-27', 479.00),
(6, 'Moni', 'Apple Watch', '2016-11-27', 269.00),
(7, 'Ranjith', 'iMac', '2016-11-28', 1999.05),
(8, 'Sathish', 'iMac', '2016-11-30', 1999.05),
(9, 'Suba', 'iPhone 7', '2016-12-07', 649.00),
(10, 'Ruben', 'Apple TV', '2016-12-14', 199.00);

CREATE TABLE `orders` (

`order_number` int(11) NOT NULL,

`customer_name` varchar(100) NOT NULL,

`purchased_items` varchar(100) NOT NULL,

`purchased_date` date NOT NULL,

`price` double(12,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

(1, 'MOhan raj', 'iPhone', '2016-11-23', 649.00),

(2, 'Siva', 'iMac', '2016-11-24', 1999.05),

(3, 'Harish', 'iPhone 5s', '2016-11-24', 299.09),

(4, 'Raja', 'Macbook Pro', '2016-11-26', 1799.50),

(5, 'Praveen', 'iPad Air 2', '2016-11-27', 479.00),

(6, 'Moni', 'Apple Watch', '2016-11-27', 269.00),

(7, 'Ranjith', 'iMac', '2016-11-28', 1999.05),

(8, 'Sathish', 'iMac', '2016-11-30', 1999.05),

(9, 'Suba', 'iPhone 7', '2016-12-07', 649.00),

(10, 'Ruben', 'Apple TV', '2016-12-14', 199.00);

index.php

<?php
$conn = mysqli_connect("localhost", "root", "", "tut");
$query = "SELECT * FROM orders ORDER BY order_number desc";
$sql = mysqli_query($conn, $query);
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | softAOX Tutorial</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.css"/>
</head>
<body>
<br/>
<div class="container">
<h2 align="center">Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL</h2>
<br/>
<br/>
<div class="col-md-2">
<input type="text" name="From" id="From" class="form-control" placeholder="From Date"/>
</div>
<div class="col-md-2">
<input type="text" name="to" id="to" class="form-control" placeholder="To Date"/>
</div>
<div class="col-md-8">
<input type="button" name="range" id="range" value="Range" class="btn btn-success"/>
</div>
<div class="clearfix"></div>
<br/>
<div id="purchase_order">
<table class="table table-bordered">
<tr>
<th width="10%">Order Number</th>
<th width="35%">Customer Name</th>
<th width="40%">Purchased Item</th>
<th width="10%">Purchased Date</th>
<th width="5%">Price</th>
</tr>
<?php
while($row= mysqli_fetch_array($sql))
{
	?>
    <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["purchased_date"]; ?></td>
    <td>$ <?php echo $row["price"]; ?></td>
    </tr>
    <?php
}
?>
</table>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.js"></script>
<!-- Script -->
<script>
$(document).ready(function(){
	$.datepicker.setDefaults({
		dateFormat: 'yy-mm-dd'
	});
	$(function(){
		$("#From").datepicker();
		$("#to").datepicker();
	});
	$('#range').click(function(){
		var From = $('#From').val();
		var to = $('#to').val();
		if(From != '' && to != '')
		{
			$.ajax({
				url:"range.php",
				method:"POST",
				data:{From:From, to:to},
				success:function(data)
				{
					$('#purchase_order').html(data);
				}
			});
		}
		else
		{
			alert("Please Select the Date");
		}
	});
});
</script>
</body>
</html>

<?php

$conn = mysqli_connect("localhost", "root", "", "tut");

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

$sql = mysqli_query($conn, $query);

<!doctype html>

<html>

<head>

<title>Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | softAOX Tutorial</title>

</head>

<body>

<br/>

<h2 align="center">Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL</h2>

<br/>

</div>

</div>

</div>

<br/>

<tr>

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

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

<th width="40%">Purchased Item</th>

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

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

</tr>

<?php

while($row= mysqli_fetch_array($sql))

{

<tr>

</tr>

<?php

}

</table>

</div>

$(document).ready(function(){

$.datepicker.setDefaults({

dateFormat: 'yy-mm-dd'

});

$(function(){

$("#From").datepicker();

$("#to").datepicker();

});

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

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

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

if(From != '' && to != '')

{

$.ajax({

url:"range.php",

method:"POST",

data:{From:From, to:to},

success:function(data)

{

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

}

});

}

else

{

alert("Please Select the Date");

}

});

</script>

</body>

</html>

range.php

<?php
// Range.php
if(isset($_POST["From"], $_POST["to"]))
{
	$conn = mysqli_connect("localhost", "root", "", "tut");
	$result = '';
	$query = "SELECT * FROM orders WHERE purchased_date BETWEEN '".$_POST["From"]."' AND '".$_POST["to"]."'";
	$sql = mysqli_query($conn, $query);
	$result .='
	<table class="table table-bordered">
	<tr>
	<th width="10%">Order Number</th>
	<th width="35%">Customer Number</th>
	<th width="40%">Purchased Item</th>
	<th width="10%">Purchased Date</th>
	<th width="5%">Price</th>
	</tr>';
	if(mysqli_num_rows($sql) > 0)
	{
		while($row = mysqli_fetch_array($sql))
		{
			$result .='
			<tr>
			<td>'.$row["order_number"].'</td>
			<td>'.$row["customer_name"].'</td>
			<td>'.$row["purchased_items"].'</td>
			<td>'.$row["purchased_date"].'</td>
			<td>'.$row["price"].'</td>
			</tr>';
		}
	}
	else
	{
		$result .='
		<tr>
		<td colspan="5">No Purchased Item Found</td>
		</tr>';
	}
	$result .='</table>';
	echo $result;
}
?>

<?php

// Range.php

if(isset($_POST["From"], $_POST["to"]))

{

$conn = mysqli_connect("localhost", "root", "", "tut");

$result = '';

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

$sql = mysqli_query($conn, $query);

$result .='

<tr>

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

<th width="35%">Customer Number</th>

<th width="40%">Purchased Item</th>

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

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

</tr>';

if(mysqli_num_rows($sql) > 0)

{

while($row = mysqli_fetch_array($sql))

{

$result .='

<tr>

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

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

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

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

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

</tr>';

}

else

{

$result .='

<tr>

<td colspan="5">No Purchased Item Found</td>

</tr>';

}

$result .='</table>';

echo $result;

}

Preview & Download

Checkout Added to cart

Mraj

Creative Designer & Developer specialist by the spirit and a loving blogger by thoughts. If you have any questions let me drop an email with the article name to the following email id: [email protected]

Ajax Drag And Drop Online Shopping Cart Using jQuery & PHP

Insert Data Into Database using AngularJS with PHP MySQL

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL

Share:

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | PART – 1

Date Range Search with jQuery DatePicker using Ajax, PHP & MySQL | PART – 2

Database

index.php

range.php

Preview & Download

Mraj

Don't miss on latest updates