php - 具有绑定参数的 PHP 变化查询。pgSQL VS MySQL 准备好的语句 | ?,?,? 对比 1 美元、2 美元、3 美元

Question

在 MySQL 中，您可以使用?绑定语句中的每个变量，这样做的好处是SQL字符串不关心变量在语句中绑定的顺序。在pgSQL我发现变量必须有一个$(number) - (e.g. $1,$2,$3). 这使得该语句不灵活，并要求该语句具有所有变量，否则$2将成为$1如果它丢失。
例子：

Table: apples

| apple_id | name |  color  | cost |
----------------------------------
|     1    |   A  |   red   | 1.50 |
|     2    |   A  |   red   | 1.60 |
|     3    |   C  |   red   | 1.70 |
|     4    |   D  |   blue  | 1.80 |
|     5    |   D  |   blue  | 2.50 |
|     6    |   F  |   gold  | 3.50 |
|     7    |   G  |   green | 4.50 |
|     8    |   H  |   green | 5.50 |
------------------------------------

//MySQL Version

//COMING FROM CLIENT | MAY BE NULL IF FILTER NOT SET

$color = "";
$name = "";
$vars = 0;
$and = "";

if(!empty($_POST['color']){
   $color = "color = ?";
   $vars++;
}
if(!empty($_POST['name']){
   $name = "name = ?";
   $vars++;
}

if($vars > 1){
   $and = "and";
}

$sql = "SELECT apple_id,name,color,cost FROM apples WHERE ".$color." ".$and." ".$name.""
$stmt = $conn->prepare($sql);

if(!empty($_POST['color']) && !empty($_POST['name'])){

    $stmt->bind_param('ss', $_POST['color'],$_POST['name']);

}elseif(!empty($_POST['color'])){
//color can be position 1
    $stmt->bind_param('s',$_POST['color']);

}elseif(!empty($_POST['name'])){
//name can be position 1
    $stmt->bind_param('s', $_POST['name']);
}
$stmt->execute();
$stmt->bind_result($id,$name,$color,$cost);
while($stmt->fetch()){
//blah blah
}

pgSQL

$color = "";
$name = "";
$vars = 0;
$and = "";

if(!empty($_POST['color']){
   $color = "color = $1";
   $vars++;
}
if(!empty($_POST['name']){
   $name = "name = $2";
   $vars++;
}

if($vars > 1){
   $and = "and";
}

$sql = "SELECT apple_id,name,color,cost FROM apples WHERE ".$color." ".$and." ".$name.""
$result = pg_prepare($conn, 'query' ,$sql);

if(!empty($_POST['color']) && !empty($_POST['name'])){
//this works if both filters are set.
    $result = pg_execute($conn,'query' , array($_POST['color'],$_POST['name']));

}elseif(!empty($_POST['color'])){
//this works if only color is set
    $result = pg_execute($conn,'query' , array($_POST['color']));

}elseif(!empty($_POST['name'])){
//this fails
    $result = pg_execute($conn,'query' , array($_POST['name']));
}
while($assoc = pg_fetch_assoc($result)){
//blah blah
}

我意识到我可以通过努力查看哪些变量以前不是空的，并帮助$_POST['name']确定$1它是否是唯一的，但这是一个非常简单的例子。我还有其他有 4 个变量的示例。
有没有更简单的方法来做到这一点？或者更灵活的方式来绑定变量？

标签： phpmysqlpostgresql