PHP AJAX datetime range probleme on SQL Query
I have a big problem and I hope I will find the solution here. After 4 days of useless search I come here to ask for a solution.
Here is my problem. I use a SQL Server database and php as server side language with AJAX to send the params to the php server.
My web app must display a list of some data if user input some date range.
Here my html form code :
<form method="post" action="">
<input type="text" placeholder="Start date : dd/mm/yyyy" id="date1" name="date1">
<input type="text" placeholder="Date de fin : dd/mm/yyy" id="date2" name="date2">
<input type="BUTTON" class="my-button" value="Show Data" onClick="LoadData()">
</form>
Here is my javascript function LoadData()
<script>
function LoadData(){
var url = "json.php";
var date1=document.getElementById('date1');
var date2=document.getElementById('date2');
var xmlhttp = new XMLHttpRequest();
var params= 'date1=' + date1 + '&date2=' + date2 ;
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
myFunction(xmlhttp.responseText);
}
}
xmlhttp.open("POST", url, true);
xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
xmlhttp.send(params);
function myFunction(response) {
var arr = JSON.parse(response);
var i;
var sum = 0;
var out = "";
for(i = 0; i < arr.length; i++)
out += '<li><div class="news_desc"><table width="100%" border="1"><tr><td width="70%"><h4><a>' +
arr[i].Name +
'</h4></td><td width="30%">' +
arr[i].Price +
'</td></tr><tr><td><p>' +
arr[i].Date +
'</p></td><td> </td></tr></table></div><div class="clear"> </div></li>' ;
}
out += ''
document.getElementById("id01").innerHTML = out;
}
}
</script>
Here is my php script
if(isset($_POST['date1']) && isset($_POST['date2']) )
{
$date1 = $_POST["date1"];
$date2 = $_POST["date2"];
$db_name = "DATABASE";
$hostname = 'IO-SD-L1';
$username = '';
$password = '';
// connect to the database
$dbh = new PDO( "sqlsrv:Server= $hostname ; Database = $db_name ", $username, $password);
$sql = "SELECT name, CAST( DateReg AS DATE ) AS dateIn, price FROM TBL_USERS
WHERE CAST( DateReg AS DATE )BETWEEN '".$date1."'AND '".$date2."'";
// the DateReg is datetime type
$stmt = $dbh->prepare( $sql );
$stmt->execute();
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
foreach ($result as $row){
$return[]=array('Name'=>$row['name'],
'Date'=>$row['dateIn'],
'Price'=>$row['price']);
}
$dbh = null;
echo json_encode($return); }
If I put the date range directly in the code like date1="12/12/2011" ; date2="11/12/2014" date1="12/12/2011" ; date2="11/12/2014" it works perfectly but if I chose the date range by user input in the two date textfield and click on the Show data button, I don't get anything.
So you are saying that when you enter a date (hard coded) it works perfectly; but when you enter the same date via a textbox control; it doesn't work? If so, I would do this: console.log(params) after you get your input data. You will find that it is not the same as you think; I suspect.
If you wish to use jQuery instead (since it simplifies the xmlhttp handlers... Try like this:
var formData = "name=johndoe&age=24"; // name value pair
// or...
var formData = { name : "johndoe", age : "24" }; // object
$.ajax({
url : "/your/url/goes/here.php",
type: "POST",
data : formData,
// if you wish to send the object as JSON, you will need to do this instead of the above...
// data: JSON.stringify(formData),
// you will also need to uncomment the next line
// contentType: "application/json; charset=utf-8",
success: function(data, textStatus, jqXHR)
{
console.log(data);
},
error: function (jqXHR, textStatus, errorThrown)
{
console.log(errorThrown);
}
});
Ref. What is content-type and datatype in an AJAX request?
I think your function not called on button click.
Check it just add
alert('check');
I first line in function body.
Also tray change onClick="LoadData()" to onClick="LoadData"
If your DATE contains TIME information, like 11/12/2014 13:30 for example. This will FAIL in the clause BETWEEN 10/12/2014 AND 11/12/2014 (using UK dd/mm/yyyy format). You must strip the TIME portion for the BETWEEN to work in the way you need it to here
上一篇: 如何使用GET发送变量而无需重新加载页面