如何用PHP+MySQL通过关联筛选表实现多条件产品查询

问题背景 #

我有两张数据表：

• products表：
  
• filters_values表：
  

需要通过名为name="specifications[]"的数组类型输入框实现搜索，筛选出所有符合条件的产品。其中ads_id与产品id一一对应，可通过SELECT * FROM product WHERE id = ads_id关联产品。

我尝试编写拼接AND条件的查询语句函数，但逻辑存在问题，现有代码及提交参数如下：

现有尝试代码 #

核心查询拼接代码 #

$query = "SELECT DISTINCT * FROM `filters_values` WHERE ";

if(empty($get['specifications'])){
    $spec = null;
}else{
    $spec = $get['specifications'];
}

$i = 0;
if($spec != null){
    foreach($spec as $data){
        $i++;
        $data = explode(',', $data);
        
        if(isset($data['1'])){
            echo 'Szukam w id filtra <b>'.$data[0].'</b> z wartością <b>'.$data['1'].'</b><br/><br/>';
            if($i > 1){
                $query .= " OR `value` LIKE '%".$data['1'].”%' “;
            }else{
                $query .= "`value` LIKE '%".$data['1'].”%' “;
            }
        }else{
            $i = ($i - 1);
        }
        
    }
}

$query .= " GROUP BY `ads_id`";

if($i == 0){
    $query = 'SELECT * FROM `filters_values`';
}

表单提交参数示例 #

array(6) { 
    ["categoryId"]=> string(5) " 1 " 
    ["text"]=> string(0) "" 
    ["region"]=> string(0) "" 
    ["Przebieg_(KM)"]=> string(2) "34" 
    ["specifications"]=> array(4) { 
        [0]=> string(4) "1,34" 
        [1]=> string(4) "2,11" 
        [2]=> string(7) "3,jakas" 
        [3]=> string(4) "4,bb" 
    } 
    ["Wpisz_coś"]=> string(2) "11" 
}

注：参数格式如"3,jakas"中，数字为筛选器ID，后面的字符串为筛选值。

完整函数代码 #

public static function getAds($get){

    $query = "SELECT DISTINCT * FROM `filters_values` WHERE ";

    if(empty($get['specifications'])){
        $spec = null;
    }else{
        $spec = $get['specifications'];
    }
    
    $i = 0;
    if($spec != null){
        foreach($spec as $data){
            $i++;
            $data = explode(',', $data);
            
            if(isset($data['1'])){
                echo 'Szukam w id filtra <b>'.$data[0].'</b> z wartością <b>'.$data['1'].'</b><br/><br/>';
                if($i > 1){
                    $query .= " OR `value` LIKE '%".$data['1'].”%' “;
                }else{
                    $query .= "`value` LIKE '%".$data['1'].”%' “;
                }
            }else{
                $i = ($i - 1);
            }
            
        }
    }
    
    $query .= " GROUP BY `ads_id`";

    if($i == 0){
        $query = 'SELECT * FROM `filters_values`';
    }
    
    echo '<br/><Br/>Koncowe zapytanie: '.$query;

    $db = new Database();
    $db->query($query);
    $db->execute();

    echo '<br/><b>Tytuły znalezionych ['.$db->rowCount().'] ogłoszeń:</b><br/><br/>';

    foreach($db->resultSet() as $data){
        if($db->rowCount() > 0){
            
                $query = ("SELECT * FROM `ads` WHERE `id` = :id ");

                if(!empty($_GET['categoryId']) && $_GET['categoryId'] > 0){
                    $query .= " AND `category_id` = :cat";
                }

                $db->query($query);

                if(!empty($_GET['categoryId']) && $_GET['categoryId'] > 0){
                    $db->bind(':cat', $_GET['categoryId']);
                }

                $db->bind(':id', $data['ads_id']);
            
            foreach($db->resultSet() as $key){
                echo '- '.$key['name'].'<br/>';
            }
        }
    }

}

问题分析 #

现有代码存在几个关键问题：

• 筛选逻辑错误：用OR连接条件会导致只要满足任意一个筛选条件就被选中，但实际需求是同时满足所有筛选条件（需要AND逻辑）
• 未关联筛选器ID：只匹配了value，没有对应到filter_id，会导致不同筛选器的相同值被错误匹配
• SQL注入风险：直接拼接用户输入到SQL语句中，存在安全隐患
• 多次查询效率低：循环中每次查询单个产品，性能较差

正确实现方案 #

核心思路 #

要筛选同时满足多个filter_id和对应value的产品，需要用分组+聚合的方式：统计每个ads_id满足的条件数量，只有数量等于筛选条件总数的才是符合要求的产品。同时使用参数绑定避免SQL注入，一次性关联查询获取产品信息。

修正后的函数代码 #

public static function getAds($get) {
    $db = new Database();
    $conditions = [];
    $bindParams = [];
    $filterCount = 0;

    // 处理specifications筛选条件
    if (!empty($get['specifications'])) {
        foreach ($get['specifications'] as $data) {
            // 限制分割为2部分，避免值含逗号的情况
            list($filterId, $filterValue) = explode(',', $data, 2); 
            if (!empty($filterId) && !empty($filterValue)) {
                $filterCount++;
                $paramName = ":filter_{$filterCount}";
                $valueParamName = ":value_{$filterCount}";
                // 每个条件对应filter_id和value的匹配
                $conditions[] = "(filter_id = {$paramName} AND `value` LIKE {$valueParamName})";
                $bindParams[$paramName] = $filterId;
                $bindParams[$valueParamName] = "%{$filterValue}%";
            }
        }
    }

    // 构建主查询
    $baseQuery = "
        SELECT p.* 
        FROM products p
        JOIN filters_values fv ON p.id = fv.ads_id
    ";

    $havingClause = "";
    if ($filterCount > 0) {
        $baseQuery .= " WHERE " . implode(" OR ", $conditions);
        $baseQuery .= " GROUP BY p.id";
        // 只有满足所有筛选条件的产品才会被保留
        $havingClause = " HAVING COUNT(DISTINCT fv.filter_id) = {$filterCount}";
    }

    // 处理categoryId筛选
    if (!empty($get['categoryId']) && trim($get['categoryId']) > 0) {
        $categoryId = trim($get['categoryId']);
        if ($filterCount > 0) {
            $baseQuery .= " AND p.category_id = :categoryId";
        } else {
            $baseQuery .= " WHERE p.category_id = :categoryId";
        }
        $bindParams[':categoryId'] = $categoryId;
    }

    $baseQuery .= $havingClause;

    // 执行查询
    $db->query($baseQuery);
    foreach ($bindParams as $param => $value) {
        $db->bind($param, $value);
    }
    $db->execute();

    // 输出结果
    echo '<br/><b>找到的产品 [' . $db->rowCount() . ']：</b><br/><br/>';
    foreach ($db->resultSet() as $product) {
        echo '- ' . $product['name'] . '<br/>';
    }
}

代码说明 #

1. 条件处理：用list($filterId, $filterValue) = explode(',', $data, 2)确保值中含逗号也能正确分割
2. 参数绑定：所有用户输入都通过参数绑定传入，彻底避免SQL注入
3. 逻辑修正：通过GROUP BY p.id和HAVING COUNT(DISTINCT fv.filter_id) = {$filterCount}确保产品满足所有筛选条件
4. 关联查询：一次性关联products和filters_values表，避免循环查询，提升效率
5. 兼容无筛选情况：当没有specifications参数时，直接查询所有产品（或加上categoryId筛选）

内容的提问来源于stack exchange，提问作者VaderStudents