PDO准备好的带有可选参数的语句 [英] PDO prepared statement with optional parameters
问题描述
我对PDO有点陌生,目前正在开发返回搜索结果的API调用.如果搜索查询有2个可选参数,如何设置prepare语句?
I'm kind of new with PDO and currently developing the API call that returns search results. How do I set a prepare statement if there are 2 optional parameters for the search query?
$app->get('/get/search', function () {
$sql = 'SELECT * FROM user WHERE name LIKE :name AND city = :city AND gender = :gender';
try {
$stmt = cnn()->prepare($sql);
$stmt->bindParam(':name', '%'.$_GET['name'].'%', PDO::PARAM_STR);
$stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
$stmt->bindParam(':gender', $_GET['gender'], PDO::PARAM_INT);
$stmt->execute();
if($data = $stmt->fetchAll()) {
echo json_encode($data);
} else {
echo json_encode(array('error' => 'no records found');
}
} catch(PDOException $e) {
echo json_encode(array('error' => $e->getMessage()));
}
}
这里的问题是 $ _ GET ['city'] 和 $ _ GET ['gender'] 是可选的.如果我尝试运行上面的代码,它将假定所有空变量也应与该列中的空值匹配;另一方面,如果我做这样的事情:
The issue here, is that both $_GET['city'] and $_GET['gender'] are optional. If I try to run the code above, it will asume that any empty variable should match an empty value in the column as well; in the other hand, if I do something like this:
if($_GET['gender']) $stmt->bindParam(':gender', $_GET['gender'], PDO::PARAM_INT);
...它将返回此错误:"SQLSTATE [HY093]:无效的参数编号:绑定变量的数量与令牌的数量不匹配"
...it will return this error: "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"
那么,如果我想保留准备好的sql语句作为可选参数的解决方案?谢谢!
So, what's the solution if I want to keep the prepared sql statement for optional parameters? Thanks!
更新
这是基于接受的答案和一些评论的解决方案(通过 deceze 和bill-karwin ):
This is the solution based on the accepted answer and some comments (by deceze and bill-karwin):
if($_GET['name']) $where[] = 'name LIKE :name';
if($_GET['city']) $where[] = 'city LIKE :city';
if(isset($_GET['gender'])) $where[] = 'gender = :gender';
if(count($where)) {
$sql = 'SELECT * FROM user WHERE '.implode(' AND ',$where);
$stmt = cnn()->prepare($sql);
$name = '%'.$_GET['name'].'%';
if($_GET['name']) $stmt->bindValue(':name', '%'.$_GET['name'].'%', PDO::PARAM_STR);
$city = '%'.$_GET['city'].'%';
if($_GET['city']) $stmt->bindParam(':city', $city, PDO::PARAM_STR);
if(isset($_GET['gender'])) $stmt->bindParam(':gender', $_GET['gender'], PDO::PARAM_BOOL);
$stmt->execute();
if($data = $stmt->fetchAll()) {
echo json_encode($data);
}
}
推荐答案
一些不错的旧动态SQL查询结合在一起...
Some good old dynamic SQL query cobbling-together...
$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
!empty($_GET['city']) ? 'AND city = :city' : null,
!empty($_GET['gender']) ? 'AND gender = :gender' : null);
...
if (!empty($_GET['city'])) {
$stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}
...
您可能可以表达得更好,并将其包装在辅助函数等中,但这是基本思想.
You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.
这篇关于PDO准备好的带有可选参数的语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!