PHP无法动态构建PDO查询。
问题描述:
So i am trying to build and prepare using PDO, a mysql query dynamically. Here is my code and debug information.
$allowed_filters = array('hotels.star_rating', 'countries.id');
$where = 'WHERE 1 ';
if (!empty($data['filters'])){
$where = 'WHERE';
foreach ($data['filters'] as $field => $value){
if (in_array($field, $allowed_filters)){
$where .= " $field = :$field &&";
}
else unset($data['filters'][$field]);
}
$where = rtrim($where, '&&');
$where = ($where == 'WHERE')? 'WHERE 1 ' : $where;
}
$st = $this->db->prepare("
SELECT
hotels.code,
hotels.name as name,
hotels.star_rating,
hotels.description,
hotels.cover_image,
countries.name as country,
cities.name as city
FROM
hotels JOIN cities ON cities.id = hotels.city_id
join countries on countries.id = cities.country_id
$where
");
$st->execute($data['filters']);
var_dump($st->fetch());
right before the line $st->execute($data['filters']) i dumped $st and $data['filters']. and the values were as follows.
Value for $st
PDOStatement Object
(
[queryString] =>
SELECT
hotels.code,
hotels.name as name,
hotels.star_rating,
hotels.description,
hotels.cover_image,
countries.name as country,
cities.name as city
FROM
hotels JOIN cities ON cities.id = hotels.city_id
join countries on countries.id = cities.country_id
WHERE
hotels.star_rating = :hotels.star_rating &&
countries.id = :countries.id
)
Value for $data['filters']
Array
(
[hotels.star_rating] => 4 stars
[countries.id] => 5
)
PDO throws an exception and fails with the following error.
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined'
Help?
答
You forgot the colon :
in the keys of $data['filters']
. It should be:
Array
(
[:hotels.star_rating] => 4 stars
[:countries.id] => 5
)