好吧,我有一个sql命令。请参见下文。
$sql = "SELECT * FROM orders
WHERE loggedtime BETWEEN :time AND :times
AND name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword
OR jlc LIKE :keyword OR odrphone LIKE :keyword
OR biid LIKE :keyword OR dstreet LIKE :keyword ";
$q=$con->prepare($sql);
$q->bindvalue(":keyword", "%".$keyword."%");
所以这个命令一直工作到第一个OR…它会检查记录的时间是否是我输入的,NAME是否像关键字。。
命令中唯一应为常量的部分是WHERE loggedtime = :time
,其余部分应基于关键字是否匹配。。。但是,如果订单上的时间不正确,它就不应该显示任何结果。
将其放入()loggedtime BETWEEN :time AND :times
中,这样您的查询应该是:
$sql = "SELECT * FROM orders
WHERE (loggedtime BETWEEN :time AND :times)
AND name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword
OR jlc LIKE :keyword OR odrphone LIKE :keyword
OR biid LIKE :keyword OR dstreet LIKE :keyword ";
逻辑AND
运算符的优先级高于逻辑OR
。MySQL文档中详细介绍了这一点。
因此,如果没有显式大括号,您当前的语句实际上如下所示:
SELECT * FROM orders
WHERE (loggedtime BETWEEN :time AND :times
AND name LIKE :keyword) OR odryname LIKE :keyword OR pdryname LIKE :keyword
OR jlc LIKE :keyword OR odrphone LIKE :keyword
OR biid LIKE :keyword OR dstreet LIKE :keyword
由于OR
的优先级低于AND
,因此需要大括号将所有OR
ed条件组合在一起:
SELECT * FROM orders
WHERE loggedtime BETWEEN :time AND :times
AND (name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword
OR jlc LIKE :keyword OR odrphone LIKE :keyword
OR biid LIKE :keyword OR dstreet LIKE :keyword)
正如您所指定的,logged time
和其他与keyword
相比的字段有两个主要条件
您需要使用大括号对逻辑相关的运算符进行分组
当组合这些条件(AND条件和OR情况)时,使用圆括号非常重要,这样数据库就知道评估每个条件的顺序
尝试此查询:
$sql = "SELECT * FROM orders
WHERE loggedtime BETWEEN :time AND :times
AND (name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword
OR jlc LIKE :keyword OR odrphone LIKE :keyword
OR biid LIKE :keyword OR dstreet LIKE :keyword) ";