Shop页面复选框多条件检索问题：AND无结果/OR结果不符合预期

问题根源 #

1. 数据分类混淆：category表同时存储了商品分类（如Duksevi、Majice）和品牌（如Nike、Adidas），但products表中category字段对应商品分类ID，brand字段对应品牌ID，两者是完全不同维度的筛选条件，不能混为一谈。
2. 查询逻辑错误：当前代码循环每个勾选的ID，执行SELECT * FROM products WHERE category IN ($brand_view) AND brand IN ($brand_view)，这要求商品的category和brand必须是同一个ID，显然不符合实际数据（比如Nike的ID是12，没有商品的category是12），所以AND条件无结果；改为OR后，会把所有匹配该ID的分类或品牌商品都查出，导致结果不符合预期。
3. SQL注入风险：直接将GET参数拼入SQL语句，存在严重安全隐患。

修复步骤 #

1. 优化数据库表结构（可选但推荐） #

在category表中新增type字段，明确区分商品分类和品牌，方便后续维护：

ALTER TABLE category ADD COLUMN type ENUM('category', 'brand') NOT NULL DEFAULT 'category';
-- 更新已有品牌数据的type字段
UPDATE category SET type = 'brand' WHERE id IN (12,13,14,15); -- Nike、Adidas、Reebok、Puma

2. 重构后端查询逻辑 #

核心是将勾选的ID拆分为商品分类ID和品牌ID，构建正确的AND条件查询，同时使用预处理语句防止SQL注入：

<div class="box-container">
    <?php
    if(isset($_GET['product_filter'])) {
        $selected_ids = $_GET['product_filter'];
        // 拆分分类ID和品牌ID（若已优化表结构，可通过查询category表的type字段区分）
        $category_ids = [];
        $brand_ids = [];
        foreach($selected_ids as $id) {
            $id = (int)$id; // 强制转为整数，避免注入风险
            if($id >=12 && $id <=15) {
                $brand_ids[] = $id;
            } else {
                $category_ids[] = $id;
            }
        }

        // 构建查询条件
        $conditions = [];
        $params = [];
        $types = '';

        if(!empty($category_ids)) {
            $placeholders = implode(',', array_fill(0, count($category_ids), '?'));
            $conditions[] = "category IN ($placeholders)";
            $params = array_merge($params, $category_ids);
            $types .= str_repeat('i', count($category_ids));
        }

        if(!empty($brand_ids)) {
            $placeholders = implode(',', array_fill(0, count($brand_ids), '?'));
            $conditions[] = "brand IN ($placeholders)";
            $params = array_merge($params, $brand_ids);
            $types .= str_repeat('i', count($brand_ids));
        }

        $sql = "SELECT * FROM products";
        if(!empty($conditions)) {
            $sql .= " WHERE " . implode(' AND ', $conditions);
        }

        // 预处理查询，避免SQL注入
        $stmt = mysqli_prepare($conn, $sql);
        if(!empty($params)) {
            mysqli_stmt_bind_param($stmt, $types, ...$params);
        }
        mysqli_stmt_execute($stmt);
        $query_run = mysqli_stmt_get_result($stmt);

        if(mysqli_num_rows($query_run) > 0) {
            foreach($query_run as $items) :
    ?>
                <form action="" method="post" class="box">
                    <input type="hidden" name="pid" value="<?= $items['id']; ?>">
                    <input type="hidden" name="name" value="<?= $items['name']; ?>">
                    <input type="hidden" name="price" value="<?= $items['price']; ?>">
                    <input type="hidden" name="image" value="<?= $items['image_01']; ?>">
                    <button class="fas fa-heart" type="submit" name="add_to_wishlist"></button>
                    <a href="quick_view.php?pid=<?= $items['id']; ?>" class="fas fa-eye"></a>
                    <img src="uploaded_img/<?= $items['image_01']; ?>" alt="">
                    <div class="name"><?= $items['name']; ?></div>
                    <div class="flex">
                       <div class="price"><span></span><?= $items['price']; ?><span> /- rsd</span></div>
                       <input type="number" name="qty" class="qty" min="1" max="99" onkeypress="if(this.value.length == 2) return false;" value="1">
                    </div>
                    <input type="submit" value="Dodaj u korpu" class="btn" name="add_to_cart">
                </form>
    <?php
            endforeach;
        } else {
            echo '<p class="empty">0 products find!</p>';
        }
    } else {
        // 默认展示所有商品（同样使用预处理语句）
        $sql = "SELECT * FROM products";
        $stmt = mysqli_prepare($conn, $sql);
        mysqli_stmt_execute($stmt);
        $query_run = mysqli_stmt_get_result($stmt);
        if(mysqli_num_rows($query_run) > 0) {
            foreach($query_run as $items) :
    ?>
                <form action="" method="post" class="box">
                    <input type="hidden" name="pid" value="<?= $items['id']; ?>">
                    <input type="hidden" name="name" value="<?= $items['name']; ?>">
                    <input type="hidden" name="price" value="<?= $items['price']; ?>">
                    <input type="hidden" name="image" value="<?= $items['image_01']; ?>">
                    <button class="fas fa-heart" type="submit" name="add_to_wishlist"></button>
                    <a href="quick_view.php?pid=<?= $items['id']; ?>" class="fas fa-eye"></a>
                    <img src="uploaded_img/<?= $items['image_01']; ?>" alt="">
                    <div class="name"><?= $items['name']; ?></div>
                    <div class="flex">
                       <div class="price"><span></span><?= $items['price']; ?><span> /- rsd</span></div>
                       <input type="number" name="qty" class="qty" min="1" max="99" onkeypress="if(this.value.length == 2) return false;" value="1">
                    </div>
                    <input type="submit" value="Dodaj u korpu" class="btn" name="add_to_cart">
                </form>
    <?php
            endforeach;
        } else {
            echo '<p class="empty">0 products find!</p>';
        }
    }
    ?>
</div>

3. 前端复选框优化（可选） #

若已优化表结构，可将分类和品牌分开渲染，提升用户体验：

<div class="navigation_categories">
    <form>
        <div>
            <h2>Kategorije</h2>
            <?php
            $select_categories = "SELECT * FROM category WHERE type = 'category'";
            $query_run = mysqli_query($conn, $select_categories);
            $checked = isset($_GET['product_filter']) ? $_GET['product_filter'] : [];
            if(mysqli_num_rows($query_run) > 0) {
                foreach($query_run as $category) {
            ?>
                <div>
                    <input type="checkbox" name="product_filter[]" value="<?= $category['id']; ?>" 
                        <?= in_array($category['id'], $checked) ? 'checked' : ''; ?>
                    />
                    <?= $category['name']; ?>
                </div>
            <?php
                }
            } else {
                echo "Trenutno nema kategorija!";
            }
            ?>
        </div>
        <div>
            <h2>Brendovi</h2>
            <?php
            $select_brands = "SELECT * FROM category WHERE type = 'brand'";
            $query_run = mysqli_query($conn, $select_brands);
            if(mysqli_num_rows($query_run) > 0) {
                foreach($query_run as $brand) {
            ?>
                <div>
                    <input type="checkbox" name="product_filter[]" value="<?= $brand['id']; ?>" 
                        <?= in_array($brand['id'], $checked) ? 'checked' : ''; ?>
                    />
                    <?= $brand['name']; ?>
                </div>
            <?php
                }
            } else {
                echo "Trenutno nema brendova!";
            }
            ?>
        </div>
        <div>
            <button type="submit" class="btn">Pretrazi</button>
        </div>
    </form>
</div>

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