优化SQL查询：处理多选分类与类型条件的正确姿势

WHERE ... AND category = 'forsale' AND category = 'torent'

WHERE ... AND type = 'house' AND type = 'apartment'

-- 逻辑错误，一个category不能同时是'forsale'和'torent'
WHERE category = 'forsale' AND category = 'torent'

-- 逻辑正确，category可以是'forsale'或'torent'
WHERE (category = 'forsale' OR category = 'torent')

// 假设 $selectedCategories 是一个包含用户选择的类别的数组，例如 ['forsale', 'torent']
$categoryConditions = [];
$categoryBindValues = [];
$index = 0;

if (!empty($selectedCategories)) {
    foreach ($selectedCategories as $category) {
        $placeholder = ':category_' . $index++;
        $categoryConditions[] = 'category = ' . $placeholder;
        $categoryBindValues[$placeholder] = $category;
    }
    if (!empty($categoryConditions)) {
        $sql .= ' AND (' . implode(' OR ', $categoryConditions) . ')';
    }
}

// 在绑定参数时，遍历 $categoryBindValues
foreach ($categoryBindValues as $placeholder => $value) {
    $stmt->bindValue($placeholder, $value, PDO::PARAM_STR);
}

WHERE field_name IN ('value1', 'value2', 'value3')

// 假设 $selectedTypes 是一个包含用户选择的类型的数组，例如 ['house', 'apartment']
$typeConditions = [];
$typePlaceholders = [];
$typeBindValues = [];

if (!empty($selectedTypes)) {
    $index = 0;
    foreach ($selectedTypes as $type) {
        $placeholder = ':type_' . $index++;
        $typePlaceholders[] = $placeholder;
        $typeBindValues[$placeholder] = $type;
    }
    $sql .= ' AND type IN (' . implode(', ', $typePlaceholders) . ')';
}

// 在绑定参数时，遍历 $typeBindValues
foreach ($typeBindValues as $placeholder => $value) {
    $stmt->bindValue($placeholder, $value, PDO::PARAM_STR);
}

public static function paginatesearch(
    $location, 
    $bedrooms, 
    $bathrooms, 
    $minamount, 
    $maxamount, 
    array $selectedCategories = [], // 接收选中的类别数组
    array $selectedTypes = [],      // 接收选中的类型数组
    $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"
            AND location = :location';

    // 动态绑定参数数组
    $bindParams = [
        ':location' => ['value' => $location, 'type' => PDO::PARAM_STR]
    ];

    if ($bedrooms !== '') {
        $sql .= ' AND bedrooms = :bedrooms';
        $bindParams[':bedrooms'] = ['value' => $bedrooms, 'type' => PDO::PARAM_INT];
    }
    if ($bathrooms !== '') {
        $sql .= ' AND bathrooms = :bathrooms';
        $bindParams[':bathrooms'] = ['value' => $bathrooms, 'type' => PDO::PARAM_INT];
    }
    if ($minamount !== '') {
        $sql .= ' AND amount >= :minamount';
        $bindParams[':minamount'] = ['value' => $minamount, 'type' => PDO::PARAM_INT];
    }
    if ($maxamount !== '') {
        $sql .= ' AND amount <= :maxamount';
        $bindParams[':maxamount'] = ['value' => $maxamount, 'type' => PDO::PARAM_INT];
    }

    // 处理多选类别
    if (!empty($selectedCategories)) {
        $categoryPlaceholders = [];
        $index = 0;
        foreach ($selectedCategories as $category) {
            $placeholder = ':category_' . $index++;
            $categoryPlaceholders[] = $placeholder;
            $bindParams[$placeholder] = ['value' => $category, 'type' => PDO::PARAM_STR];
        }
        $sql .= ' AND category IN (' . implode(', ', $categoryPlaceholders) . ')';
    }

    // 处理多选类型
    if (!empty($selectedTypes)) {
        $typePlaceholders = [];
        $index = 0;
        foreach ($selectedTypes as $type) {
            $placeholder = ':type_' . $index++;
            $typePlaceholders[] = $placeholder;
            $bindParams[$placeholder] = ['value' => $type, 'type' => PDO::PARAM_STR];
        }
        $sql .= ' AND type IN (' . implode(', ', $typePlaceholders) . ')';
    }

    // 排序逻辑保持不变
    if ($sortby === 'newest') {
        $sql .= "\nORDER BY posts.created_at DESC";
    } elseif ($sortby === 'oldest') {
        $sql .= "\nORDER BY posts.created_at ASC";
    } elseif ($sortby === 'highest') {
        $sql .= "\nORDER BY posts.amount DESC";
    } elseif ($sortby === 'lowest') {
        $sql .= "\nORDER BY posts.amount ASC";
    }

    $stmt = $db->prepare($sql);

    // 统一绑定所有参数
    foreach ($bindParams as $paramName => $paramData) {
        $stmt->bindValue($paramName, $paramData['value'], $paramData['type']);
    }

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

<!-- Categories -->
<input type="checkbox" name="categories[]" value="forsale"> For Sale
<input type="checkbox" name="categories[]" value="torent"> To Rent

<!-- Types -->
<input type="checkbox" name="types[]" value="house"> House
<input type="checkbox" name="types[]" value="apartment"> Apartment

$selectedCategories = isset($_GET['categories']) ? (array)$_GET['categories'] : [];
$selectedTypes = isset($_GET['types']) ? (array)$_GET['types'] : [];

$results = YourClass::paginatesearch(
    $location, 
    $bedrooms, 
    $bathrooms, 
    $minamount, 
    $maxamount, 
    $selectedCategories, // 传递数组
    $selectedTypes,      // 传递数组
    $sortby
);