使用PDO prepare和bind语句的条件查询

问题描述

我将所有查询从mysql转换为PDO，在此过程中，我发现了如下条件查询

I am converting all my queries from mysql to PDO, and in this process I found a conditional query like a follows

if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE '%{$parameters['searchTerm'] }%'";
}

$sql = "Select * from table data Where tableId = 5 {$where} ";

当我尝试在PDO中转换此查询时，预期的语法如下

and when I am trying to convert this query in PDO the expected syntax is as follows

if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE :searchTerm";
}



$sql = $dbh->prepare("Select * from table data Where tableId = 5 {$where}");



if (isset($parameters['searchTerm'])) {
    $sql ->bindParam(':searchTerm', '%{$parameters['searchTerm'] }%');
}

$sql ->execute();

现在您可以看到if条件 if(isset($ parameters ['searchTerm'])){...} 重复了两次.

Now as you can See that the if condition if (isset ($parameters ['searchTerm'] )) {...} is repeated twice.

原因是

1. 在设置$ where之前我无法准备sql查询，因此在第一个if语句之后初始化$ sql变量
2. 在准备sql之前，我无法绑定参数，因此必须在准备$ sql之后将其放置

因此，在 $ sql = $ dbh-&prepare(从表数据中选择* where tableId = 5 {$ where}");之前有一个if语句； 之后是一个if语句.

So there is one if statement before $sql = $dbh->prepare("Select * from table data Where tableId = 5 {$where}"); and one if statement after.

我的问题是:有没有一种方法可以删除此多余的if语句，或者我只需要这样做即可.

And my question is: Is there a way to remove this redundant if statement or I have to do it this way only.

推荐答案

您可以使用方便的PDO功能，该功能使您可以将带有参数的数组直接发送到execute()

you can use handy PDO's feature that lets you to send array with parameters straight into execute()

$where  = '';
$params = array();
if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE :searchTerm";
    $params['searchTerm'] = "%$parameters[searchTerm]%";
}
$sql = "Select * from table data Where tableId = 5 $where";
$pdo->prepare($sql)->execute($params);

请注意，代码中的PHP语法也是错误的.

Note that PHP syntax in your code is also wrong.

这篇关于使用PDO prepare和bind语句的条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！