how generate report between the two dates using datepicker,ajax,php,mysql.?

Question

I have been given a task to generate a report between two given dates using datepicker,ajax,php and mysql. below is my html:

Date wise Report

        From date: <input type="text" id="fromdate" value="">   To date: <input type="text" id="todate" value="" onChange="showuser(document.getElementById('fromdate').value,document.getElementById('todate').value)">  
        <br>
        <div id="txtHint"><b>User informathions will be listed here.</b></div>

Scripts:

<script>
  $(function() {
    $( "#fromdate" ).datepicker();
    $( "#todate" ).datepicker();
  });
  </script>

<script type="text/javascript">
function showUser(fromdate,todate)
{
  if (fromdate =="" && todate=="")
    {
     document.getElementById("txtHint").innerHTML="";
     return;
    } 

    if (window.XMLHttpRequest)
     {// code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
     }
  else
    {// code for IE6, IE5
     xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }

  xmlhttp.onreadystatechange=function()
    {
     if (xmlhttp.readyState==4 && xmlhttp.status==200)
      {
        document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
      }
    }
    xmlhttp.open("GET","bet_date.php?fromdate="+fromdate+"&todate="+todate,true);
    xmlhttp.send();
}
</script>

Here is the php file which is supposed to generate the report: bet_date.php

include("database.php"); 
$fromdate=$_GET["fromdate"];
$todate=$_GET["todate"];
 $sql = "SELECT * FROM bookings WHERE date between '$fromdate' and '$todate'";
 $result = mysql_query($sql);

 echo "<table border='1'>
<tr>
<th>id</th>
<th>date</th>
<th>start</th>
<th>name</th>
<th>email</th>
<th>phone</th>
<th>comments</th>
<th>approved</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['start'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
   echo "<td>" . $row['phone'] . "</td>";
    echo "<td>" . $row['comments'] . "</td>";
     echo "<td>" . $row['approved'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

The problem is when I select both the date then nothing happens. kindly help me in this situation. simple examples would be greatly appreciated. Thanks.

Does the php return something if both date are present? DB has record for both cases? — Marco Mura
– Marco Mura, Commented Dec 18, 2014 at 10:41
yes, i tried it in phpMyadmin using: SELECT * FROM bookings WHERE date between '2014-12-09' and '2014-12-13' and it returned the result properly — Dinesh Suthar
– Dinesh Suthar, Commented Dec 18, 2014 at 10:47

Mahendra Pumbhadiya · Accepted Answer · 2014-12-18 11:23:41Z

I have Created Whole Tutorial for Date Wise Report, so once try it

Table structure

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `start` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `comments` longtext NOT NULL,
  `approved` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `bookings` (`id`, `date`, `start`, `name`, `email`, `phone`, `comments`, `approved`) VALUES
(1, '2014-12-17', 'yes', 'Mahendra', 'mahendra@XXXX', '89XXXXXXXX', 'nothing', 'yes'),
(2, '2014-12-18', 'no', 'Rahul', 'rahul@XXXXXX', '987XXXXXXX', 'very nice article', 'yes');

my_test.php

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
  $("#generate_report").click(function(){
	  var from_date=jQuery("#fromdate").val();
	  var to_date=jQuery("#todate").val();
	  var data =
	  {		
	  	from_date	 : from_date,
		to_date		 : to_date
	  }
	jQuery.ajax({	
					type: "POST",
					url: "test.php",
					data: data,
					success: function(responce){
						$("#txtHint").after(responce);
				  }	
			 });
  });
});
</script>

From date: <input type="text" id="fromdate" value="2014-12-17">   To date: <input type="text" id="todate" value="2014-12-18">  
<input type="button" name="generate_report" id="generate_report" value="Generate Report" />
<br>
<div id="txtHint"><b>User informations will be listed here.</b></div>

test.php (ajax calling file)

<?php
error_reporting(0);
include_once("wp-config.php");
extract($_POST);
$sql1=mysql_query("Select * from bookings where date between '".$from_date."' AND '".$to_date."'");
echo "<table border='1'>
	  <tr>
		<th>id</th>
		<th>date</th>
		<th>start</th>
		<th>name</th>
		<th>email</th>
		<th>phone</th>
		<th>comments</th>
		<th>approved</th>
	 </tr>";
		while($row = mysql_fetch_array($sql1))
		{
		  echo "<tr>";
			  echo "<td>" . $row['id'] . "</td>";
			  echo "<td>" . $row['date'] . "</td>";
			  echo "<td>" . $row['start'] . "</td>";
			  echo "<td>" . $row['name'] . "</td>";
			  echo "<td>" . $row['email'] . "</td>";
			  echo "<td>" . $row['phone'] . "</td>";
			  echo "<td>" . $row['comments'] . "</td>";
			  echo "<td>" . $row['approved'] . "</td>";
		  echo "</tr>";
		}
echo "</table>";
?>

this demo is perfectly working and just now i have created so once check it...

enter image description here

Please Anyone get interest than please correct answer and give vote for this solution.. Thanks

Suchit kumar · Accepted Answer · 2014-12-18 11:05:14Z

1

change your html to this:you have showUser() not showuser() so change in input onchage="showUser()".

write onchange event to both the inputs.so on both fields they will trigger. and in your sql use date(date) if you are sending only date from front-end and date column in database is of type datetime..

"SELECT * FROM bookings WHERE date(date) between '$fromdate' and '$todate'";


  From date: <input type="text" id="fromdate" value="" onChange="showUser()">   To date: <input type="text" id="todate" value="" onChange="showUser()"> 

function showUser()
{

var fromdate = $( "#fromdate" ).val();
var todate= $( "#todate" ).val();

// rest of your code:




}

hope you are getting post/get parameters properly in php.

edited Dec 18, 2014 at 11:05

answered Dec 18, 2014 at 10:44

Suchit kumar

11.9k3 gold badges26 silver badges46 bronze badges

9 Comments

Dinesh Suthar Over a year ago

I am using GET,is it correct syntax: xmlhttp.open("GET","bet_date.php?fromdate="+fromdate+"&todate="+todate,true);

Dinesh Suthar Over a year ago

date is of type date only

Suchit kumar Over a year ago

just use echo $sql; and see what it gives.

Dinesh Suthar Over a year ago

if that big table is not visible then how come echo $sql would be visible,first thing i wish ask you,are those my above files correct?

Suchit kumar Over a year ago

yes they looks correct.use print_r($sql); and see in console see what it gives.

|

geisterfurz007 · Accepted Answer · 2019-07-23 07:54:26Z

0

Check whether the date passed by the datepicker using AJAX is in valid format suitable for your query.

Try to display these two variables passed by HTML datepicker in your PHP page:

echo $_GET["fromdate"];
echo $_GET["todate"];

This will give you a hint of any issue if there.

edited Jul 23, 2019 at 7:54

geisterfurz007

5,9246 gold badges37 silver badges59 bronze badges

answered Dec 18, 2014 at 11:11

Suvojit

3771 gold badge7 silver badges24 bronze badges

Collectives™ on Stack Overflow

how generate report between the two dates using datepicker,ajax,php,mysql.?

Date wise Report

3 Answers 3

Comments

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Date wise Report

3 Answers 3

Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related