我正在尝试使用Ajax和PHP通过下拉菜单检索数据。
选择选项值与数据库列名称配对时可以很好地工作,但是当我将其更改为特定于日期时,它将不起作用。
请通过下面的代码检查并建议所需的更改。
HTML:
<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">July</option> // I doesn't work if when I change the value to "\'2015-07-01\' AND \'2015-07-31\'"
<option value="2">August</option> // I doesn't work if when I change the value to "\'2015-08-01\' AND \'2015-08-31\'"
<option value="3">September</option> // I doesn't work if when I change the value to "\'2015-09-01\' AND \'2015-09-30\'"
<option value="4">October</option> // I doesn't work if when I change the value to "\'2015-10-01\' AND \'2015-10-31\'"
</select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>
Java脚本
function showUser(str) {
if (str == "") {
document.getElementById("txtHint").innerHTML = "";
return;
} else {
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","getleads.php?q="+str,true);
xmlhttp.send();
}
}
PHP:
<?php
$q = intval($_GET['q']);
/*Comments Start, I even tried if else with the select values being only numeric
if($q==1)
{
$q == "\'2015-07-01\' AND \'2015-07-31\'";
}
elseif ($q==2)
{
$q == "\'2015-08-01\' AND \'2015-08-31\'";
}
elseif ($q==3)
{
$q == "\'2015-09-01\' AND \'2015-09-30\'";
}
} elseif ($q==4){
$q == "\'2015-10-01\' AND \'2015-10-31\'";
} else ($q== )
{
$q == "*";
}
Comments End*/
$con = mysqli_connect('localhost','user','password','database');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}
mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM table WHERE ID = '".$q."'";
$result = mysqli_query($con,$sql);
echo "<table>
<tr>
<th>Name</th>
<th>Number</th>
<th>Email</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['frm_name'] . "</td>";
echo "<td>" . $row['frm_mobile'] . "</td>";
echo "<td>" . $row['frm_email'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
我什至尝试删除日期中的反斜杠。
请帮忙 !!
最佳答案
您不应在sql查询中注入变量;现在,在服务器端对字符串进行硬编码并没有什么关系,但是如果您更改格式以接受来自表单的值,则可以对sql注入打开查询。
您应该改用准备好的语句;那么您就不需要引号,并且如果将来进行任何更改,也不会偶然添加任何错误。
除此之外,您生成的sql是错误的,请检查取消注释字符串分配时发生的情况:
SELECT * FROM table WHERE ID = ''2015-07-01' AND '2015-07-31''
两个引号都是错误的,而
AND
语法是错误的。并假设table
不是您的真实表名。您可能想要类似:
SELECT * FROM your_table WHERE your_date_field BETWEEN ? AND ?
您可以在其中将任何有效的格式化日期绑定到问号的位置。
请注意,mysql还具有
MONTH()
函数,在这种特定情况下,该查询会使查询变得容易得多。