我想问一下,我可以把multiple-IN运算符与和查询结合起来吗?我需要你帮我写代码。这是我试过但没用的代码。
if ($areas != '' || $city_check != '' || $course != '' || $subject_check != '') {
$countcity = count($city_check);
$countsubject = count($subject_check);
$area_ids = implode(',',$city_check);
$subject_ids = implode(',',$subject_check);
$sql .= "AND TAC.tac_st_id = '".$areas."' AND TAC.tac_city_id IN($area_ids)
AND TRS.trs_tc_id = '".$course."' AND TRS.trs_ts_id IN($subject_ids)
GROUP BY U.u_username
HAVING COUNT(distinct TAC.tac_city_id) = '".$countcity."' COUNT(distinct TRS.trs_ts_id) = '".$countsubject."'
";
}
没有结果。
我已经做了下面的代码,我想工作,但它只能运行每一块内的if。所以,如果我想搜索城市检查,主题检查将不工作,反之亦然。
//search for cities
// if ($areas != '' || $city_check != '') {
// $countcity = count($city_check);
// $sql .= "AND TAC.tac_st_id = '".$areas."' AND TAC.tac_city_id IN(".implode(',',$city_check).")
// GROUP BY U.u_username
// HAVING COUNT(distinct TAC.tac_city_id) = '".$countcity."'
// ";
// }
//search for subject
// if ($course != '' || $subject_check != '') {
// $countsubject = count($subject_check);
// // echo json_encode(["message"=>$countcity]);
// $sql .= "AND TRS.trs_tc_id = '".$course."' AND TRS.trs_ts_id IN(".implode(',',$subject_check).")
// GROUP BY U.u_username
// HAVING COUNT(distinct TRS.trs_ts_id) = '".$countsubject."'
// ";
// }
我想要的是城市和主题都可以一起搜索。谢谢你在高级。。
编辑:我试图在phpmyadmin中输出我的SQL
SELECT U.u_username,U.u_email,U.u_status,ST.st_name,CT.city_name,TC.tc_title,TS.ts_title
FROM tk_user as U
INNER JOIN tk_user_details AS UD ON UD.ud_u_id = U.u_id
LEFT JOIN tk_tutor_subject AS TRS ON TRS.trs_u_id = U.u_id
LEFT JOIN tk_tution_course AS TC ON TC.tc_id = TRS.trs_tc_id
LEFT JOIN tk_tution_subject AS TS ON TS.ts_id = TRS.trs_ts_id
LEFT JOIN tk_tutor_area_cover AS TAC ON TAC.tac_u_id = U.u_id
LEFT JOIN tk_cities CT ON CT.city_id = UD.ud_city AND TAC.tac_city_id = CT.city_id
LEFT JOIN tk_states AS ST ON TAC.tac_st_id = ST.st_id
LEFT JOIN tk_countries AS C ON ST.st_c_id = C.c_id AND CT.city_c_id = C.c_id
WHERE U.u_status <> 'D'
AND TAC.tac_st_id = 1046 AND TAC.tac_city_id IN(16,17)
AND TRS.trs_tc_id = 5 AND TRS.trs_ts_id IN(8,9)
GROUP BY U.u_username
HAVING COUNT(distinct TAC.tac_city_id) = 2 AND COUNT(distinct TRS.trs_ts_id) = 2
最佳答案
如果其中一个可能是空的,你应该使用这样的东西:
使用OR
...
WHERE U.u_status <> 'D'
AND
(
(TAC.tac_st_id = 1046 AND TAC.tac_city_id IN(16,17) )
OR
(TRS.trs_tc_id = 5 AND TRS.trs_ts_id IN(8,9) )
)
...
关于php - 多个IN运算符mysql结合,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50703389/