Zend框架SQL选择查询构造(WHERE子句)
发布于 2021-05-10 21:23:59
我正在尝试生成一个查询,该查询从用户名中全选,其中姓氏的姓氏的任何组合都与特定搜索词匹配
$select = $select->where('last_name LIKE ?', '%'.$term.'%')->orWhere('first_name LIKE ?', '%'.$term.'%')
->orWhere("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%')
->orWhere("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');
还必须满足另一个条件,该条件在另一个where子句中指定
$select = $select->where("deleted = 0 AND scholar = 0");
生成以下SQL语句
SELECT `user`.* FROM `user` WHERE (last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%') AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` desc LIMIT 25
这并没有返回期望的结果,因为我得到了Scholar = 1的行;
我认为查询应该是
SELECT `user`.* FROM `user` WHERE ((last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%')) AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` DESC LIMIT 25
使用$ select对象实现此目的的正确语法是什么。
关注者
0
被浏览
48
1 个回答
-
您可以使用quoteIn来准备您的条件,然后像这样使用它们:
$first_name_cond = $db->quoteInto('first_name LIKE ?', '%'.$term.'%'); $last_name_cond = $db->quoteInto('last_name LIKE ?', '%'.$term.'%'); $concat_cond1 = $db->quoteInto("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%'); $concat_cond2 = $db->quoteInto("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%'); $select = $select->where($first_name_cond.' OR '.$last_name_cond.' OR '. $concat_cond1.' OR '.$concat_cond2)->where("deleted = 0 AND scholar = 0");