我想问一下,我可以把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

php - 多个IN运算符mysql结合-LMLPHP
php - 多个IN运算符mysql结合-LMLPHP
php - 多个IN运算符mysql结合-LMLPHP

最佳答案

如果其中一个可能是空的,你应该使用这样的东西:
使用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/

10-10 13:12