SQL复选框和多选择过滤 - sql checkbox and mulit select filtering

sql checkbox and mulit select filtering

本文关键字：选择过滤复选框 SQL | 更新日期: 2023-09-27

我有一些问题过滤我的用户搜索时运行。必须能够选择一个以上的地区和性别。性别是复选框，区域是选择多个选择器。

在我的项目中，所有的内容都是动态的，但是在这里要展示的太多了。

的形式:

<form action="" method="get">
    <input type="checkbox" name="gender[]" value="1"> <!-- male -->
    <input type="checkbox" name="gender[]" value="2"> <!-- female -->
    <select name="region[]" multiple>
        <option value="1">North</option>
        <option value="2">East</option>
        <option value="3">West</option>
        <option value="4">South</option>
    </select>
    <input type="submit" name="submitSearch" value="Filter">
</form>

过滤功能:

<?php
    if(isset($_GET['submitSearch']){
        user_filter($db);
    }
    function user_filter($db){
        $gender = $_GET['gender'];
        $region = $_GET['region'];
        $sql = "SELECT name, region, img FROM users WHERE true $gender AND $region";
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $res = $stmt->fetchAll();
        return $res;
    }
?>

不确定我是否对数组进行了循环，如果没有设置

， WHERE为真会出现问题

希望有人能帮助我由于

像这样修改代码

if(isset($_GET['submitSearch']){
    user_filter($db);
}
function user_filter($db){
    $gender = implode(",",$_GET['gender']); //Change array to comma separated string so easy to pass in mysql using IN keyword
    $region = implode(",",$_GET['region']);
    $sql = "SELECT name, region, img FROM users WHERE gender IN($gender) AND region IN ($region)";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetchAll();
    return $res;
}

条件

的更新代码

function user_filter($db){
        if(isset($_GET['gender']) && $_GET['gender'] !=''){
            $gender = implode(",",$_GET['gender']); //Change array to comma separated string so easy to pass in mysql using IN keyword
        }
        if(isset($_GET['region']) && $_GET['region'] !=''){
            $region = implode(",",$_GET['region']);
        }

        $genderSql = "";
        $regionSql = "";
        $where = "";
        $sql = '';
        $sql .="SELECT name, region, img FROM users";
        if(isset($_GET['gender']) && $_GET['gender'] !=''){
            $genderSql =" gender IN($gender)"; //Note Space at start 
        }
        if(isset($_GET['region']) && $_GET['region'] !=''){
            if(isset($_GET['gender']) && $_GET['gender'] !=''){
                $regionSql =" AND region IN ($region)"; //Note Space at start 
            } else {
                $regionSql =" region IN ($region)"; //Note Space at start 
            }
        }

        if((isset($_GET['gender']) && $_GET['gender'] !='') || (isset($_GET['region']) && $_GET['region'] !='')){
            $where =" Where";
            $sql .=$where.$genderSql.$regionSql;
        }

        $stmt = $db->prepare($sql);
        $stmt->execute();
        $res = $stmt->fetchAll();
        return $res;
    }