这是一个语义正确的SQLite搜索查询吗?
问题描述:
编辑** 不知道这个额外的信息会有帮助,但试图保持简单。这是一个语义正确的SQLite搜索查询吗?
用户通过html表单选择数据(最多三个“性别”选项,最多六个“颜色”,然后可以选择一个单一的价格范围),然后这个数据被处理jquery函数来构造下面的字符串并将其保存在一个变量中。然后将该变量用作sql查询,下面显示的查询字符串是此变量的输出。
我在Chrome的开发者工具进行调试时,有没有表现出错误**
这有什么不对的查询字符串?
'SELECT * FROM flipflops WHERE colour="black" OR colour="brown" AND gender="mens" OR gender="womens" AND price<=20'
值正确,因为它们与db匹配。说实话,它似乎回到了它所喜欢的东西。我希望对于某人来说,我已经做错了,因为我是一个无知的新手。
任何帮助将不胜感激! :d
编辑** 上面现在所取代,后者当你复制它,因为它是工作的完善!
'SELECT * FROM flipflops WHERE colour IN ("white","navy","blue") AND gender IN ("mens","juniors") AND price >= 20 AND price <= 30'
我在使用正则表达式来创建jQuery的字符串如下离开了pricerange现在但它从数据库返回任何内容,我没有得到控制台错误
var regExp = /\(([^)]+)\)/;
var queryValue = regExp.exec("SELECT * FROM flipflops WHERE colour IN /(\'" + colourValues.join("','") + "/')\ AND gender IN /(\'" + genderValues.join("','") + "/')\"");
输出到控制台值依赖于形式的价值选择(抱歉复杂此,我太乱了!)
Array[2]
0: "('white','blue/')"
1: "'white','blue/'"
index: 41input:
"SELECT * FROM flipflops WHERE colour IN /('white','blue/') AND gender IN /('womens','juniors/')""
length: 2
答
你可能错过了括号?
SELECT * FROM flipflops
WHERE
(
colour="black"
OR colour="brown"
)
AND
(
gender="mens"
OR gender="womens"
)
AND price<=20
,如果是这样的话,你可以把它写这样还有:
SELECT * FROM flipflops
WHERE colour IN ("black","brown")
AND gender IN ("mens","womens")
AND price<=20
答
下面的代码创建使用Arions IN()操作例如动态查询。查询是建立取决于什么被点击框等
<form action="#" method="post">
<input type="checkbox" name="color[]" value="Red"><label>Red</label><br/>
<input type="checkbox" name="color[]" value="Blue"><label>Blue</label><br/>
<input type="checkbox" name="color[]" value="Brown"><label>Brown</label><br/>
<!--input type="checkbox" name="gender[]" value="Any"><label>Any</label><br/-->
<br>
<input type="checkbox" name="gender[0]" value="Male"><label>Male</label><br/>
<input type="checkbox" name="gender[1]" value="Female"><label>Female</label><br/>
<br>
<input type="text" name="cost" ><label>Cost</label><br/>
<input type="submit" name="submit" value="Submit"/>
</form>
<?php
// Set up stub of query
$sql = "SELECT * FROM flipflops ";
//Set initial WHERE clause
$clause ="WHERE ";
if(isset($_POST['submit'])){//to run PHP script on submit
//Color set up IN() operator
if(!empty($_POST['color'])){
$sql .= $clause." colour IN (";
// Loop to store and display values of individual checked checkbox.
foreach($_POST['color'] as $color){
$sql .= $color.",";
}
$sql = rtrim($sql, ",");
$sql .= ")";
//Set to AND after initial WHERE
$clause =" AND ";
}
//Gender show only one gender, if 2 genders clicked all 2 genders are selected
if(!empty($_POST['gender']) && count($_POST['gender']) == 1){
// Loop to store and display values of individual checked checkbox.
foreach($_POST['gender'] as $gender){
$sql .= $clause. "gender = ".$gender;
}
$clause =" AND ";
}
//Cost
if(!empty($_POST['cost'])){
$sql .= $clause." cost = ". $_POST['cost'];
}
}
echo $sql;
?>
由于只有2分性别的'和性别=“男士”或性别=“女子”'是多余的。 – 2014-12-05 10:32:53
@david strachan这会阻止它的工作? – RyanUK 2014-12-05 11:23:51
@davidstrachan我已添加更多信息 – RyanUK 2014-12-05 15:09:26