优化SQL多选查询：正确处理AND与OR逻辑及IN操作符的应用

... WHERE coverimage != "default.jpg" AND location = :location
AND category = :forsale
AND category = :torent
AND type = :house
AND type = :apartment

SELECT ...
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE coverimage != "default.jpg"
AND location = :location
AND (category = 'forsale' OR category = 'torent') -- 注意括号，将OR条件分组
AND (type = 'house' OR type = 'apartment')         -- 注意括号
...

// 假设 $selectedCategories 和 $selectedTypes 是从表单获取的已选择值数组
$categoryConditions = [];
$typeConditions = [];
$bindValues = []; // 存储所有需要绑定的参数

// 处理 Category 选项
if (!empty($forsale)) {
    $categoryConditions[] = 'category = :forsale_val';
    $bindValues[':forsale_val'] = $forsale;
}
if (!empty($torent)) {
    $categoryConditions[] = 'category = :torent_val';
    $bindValues[':torent_val'] = $torent;
}
if (!empty($holidayaccommodation)) {
    $categoryConditions[] = 'category = :holiday_val';
    $bindValues[':holiday_val'] = $holidayaccommodation;
}

// 处理 Type 选项 (此处仅为示例，其他类型处理类似)
if (!empty($house)) {
    $typeConditions[] = 'type = :house_val';
    $bindValues[':house_val'] = $house;
}
if (!empty($apartment)) {
    $typeConditions[] = 'type = :apartment_val';
    $bindValues[':apartment_val'] = $apartment;
}
// ... 其他 type 条件类似处理

// 如果有选中的类别，则添加到SQL中
if (!empty($categoryConditions)) {
    $sql .= ' AND (' . implode(' OR ', $categoryConditions) . ')';
}
// 如果有选中的类型，则添加到SQL中
if (!empty($typeConditions)) {
    $sql .= ' AND (' . implode(' OR ', $typeConditions) . ')';
}

// 在 prepare 之后，遍历 $bindValues 数组来绑定参数
// foreach ($bindValues as $key => $value) {
//     $stmt->bindValue($key, $value, PDO::PARAM_STR); // 根据实际类型调整
// }

<?php

class Post
{
    // 假设 getDB() 方法用于获取数据库连接
    private static function getDB()
    {
        // 实际应用中应返回一个PDO对象
        // 例如：return new PDO('mysql:host=localhost;dbname=yourdb', 'user', 'password');
        return new PDO('sqlite::memory:'); // 示例使用内存SQLite
    }

    public static function paginatesearch(
        $location, $bedrooms, $bathrooms, $minamount, $maxamount,
        $forsale, $torent, $holidayaccommodation, // category options
        $house, $apartment, $duplex, $simplex, $lodge, $campsite, $hotel, $commercial, $industrial, $land, // type options
        $sortby
    ) {
        $db = static::getDB();

        $sql = 'SELECT *,
                posts.id as postId,
                users.id as userId,
                posts.created_at as postCreated,
                users.created_at as userCreated
                FROM posts
                INNER JOIN users
                ON posts.user_id = users.id
                WHERE coverimage != "default.jpg"'; // 初始条件

        $bindParams = []; // 收集所有需要绑定的参数

        // 绑定 location 参数
        if (!empty($location)) {
            $sql .= ' AND location = :location';
            $bindParams[':location'] = $location;
        }

        // 处理数值型过滤条件
        if (!empty($bedrooms)) {
            $sql .= ' AND bedrooms = :bedrooms';
            $bindParams[':bedrooms'] = (int)$bedrooms; // 确保是整数
        }
        if (!empty($bathrooms)) {
            $sql .= ' AND bathrooms = :bathrooms';
            $bindParams[':bathrooms'] = (int)$bathrooms;
        }
        if (!empty($minamount)) {
            $sql .= ' AND amount >= :minamount';
            $bindParams[':minamount'] = (int)$minamount;
        }
        if (!empty($maxamount)) {
            $sql .= ' AND amount <= :maxamount';
            $bindParams[':maxamount'] = (int)$maxamount;
        }

        // 处理 Category 选项，使用 IN 操作符
        $selectedCategories = [];
        if (!empty($forsale)) $selectedCategories[] = $forsale;
        if (!empty($torent)) $selectedCategories[] = $torent;
        if (!empty($holidayaccommodation)) $selectedCategories[] = $holidayaccommodation;