代码之家 › 专栏 › 技术社区 › Andrew

如果数组中存在特定值,如何将值动态传递到where条件?

sql php

Andrew · 技术社区 · 6 年前

WHERE 声明。例如,我有这样的数组

Array
(
    [project_name] => Riverside
    [property_type] => For Rent
    [rooms] => default
)

如果rooms值等于default,我不应该对rooms使用WHERE语句,所以看起来像这样。

$sql = SELECT Property_Type, Property_Name, Rooms from  appartment
             WHERE Project_Name = :project_name AND
              Property_Type = :property_type;

$sql = SELECT Property_Type, Property_Name, Rooms from  appartment
             WHERE Project_Name = :project_name AND
              Property_Type = :property_type AND Rooms = 2;

我不想编写单独的sql语句,因为这同样适用于 project_name 如果值为 All projects 它不应该使用 Project_Name 在Where语句中。

1 回复 | 直到 6 年前

Justinas 6 年前

动态生成查询:

$sql = 'SELECT Property_Type, Property_Name, Rooms from  appartment';
$where = [];
$params = [];

foreach ($_GET['WhereConditions'] as $key => $value) {
    switch($key) {
        case 'rooms':
            if ($value == 2) {
                $where[] = "`Rooms` = :roomsCount";
                $params[':roomsCount'] = 2;
            }

            break;
         ...
    }
}

if (!empty($where)) {
     $sql .= ' WHERE '.implode('AND', $where);
}

$db->prepare($sql)->fetchAll($params);

推荐文章