如何在PHP中制作动态的Postgres预准备语句 [英] How to make dynamic postgres prepared statements in PHP

问题描述

我正在尝试使用postgres在PHP中编写一些准备好的语句。

I'm trying to make some prepared statements in PHP using postgres.

解释起来有点困难，所以我只向您展示：

It's a bit difficult to explaing so i'll just show you:

$stmt = "SELECT * FROM customer WHERE zip = '$1'";

if(isset($_POST["CITY"])){ 
   $stmt .= "AND city = '$2'";
}

if(isset($_POST["COUNTRY"])){ 
   $stmt .= "AND country = '$3'";
}

$result = pg_prepare("myconnection", "my query", $stmt);

$result1 = pg_execute("myconnection","my query", array("0000","someCity","someCountry"));

很抱歉，如果某些代码错误，但这是徒手的例子。我需要的是能够使准备好的语句动态化，具体取决于是否设置了某些变量/非空值。
当语句仅期望1或如果我只需要添加$ 1和$ 3而不是$ 2时，在数组中发布3个变量时，似乎不起作用。希望您能理解。

Sorry if some of the code is wrong but it's a freehand example. What I need is to be able to make the prepared statement dynamic depending on if some variables isset/not-null. It doesn't seem to work when posting 3 variables in the array when the statement only expects 1 or if i only need to add $1 and $3 but not $2. I hope you understand.

这个周末我需要使用它，所以希望有人知道！

I need to use it this weekend, so I hope someone knows!

谢谢

推荐答案

拥有3种不同的语句（每种情况一个）并执行适用的语句没有错取决于传递的参数数量。
示例：

There is nothing wrong in having 3 different statements (one for each case) and execute the one that applies depending on the number of parameters passed. Example:

编辑：我修改了代码以匹配所有情况：

• 仅指定邮政编码


• 邮政编码+城市


• 邮政编码+国家


• 邮政编码+城市+国家

• Only the zip specified
• Zip + city
• Zip + country
• Zip + city + country

（即使有其他情况，您也会明白的）

(even if there are some other cases, you'll understand the idea)

$stmt = "SELECT * FROM customer WHERE zip = '$1'";

if(isset($_POST["CITY"]) && isset($_POST["COUNTRY"])) { 
   $stmt3 = $stmt . " AND city = '$2'" . " AND country = '$3'";
} elseif(isset($_POST["CITY"])) { 
   $stmt1 = $stmt . " AND city = '$2'";
} elseif(isset($_POST["COUNTRY"])) {
   $stmt2 = $stmt . " AND country = '$2'";
}

if(isset($stmt3)) {
   $result = pg_prepare("myconnection", "my query", $stmt3);
   $result1 = pg_execute("myconnection","my query", array("0000","someCity","someCountry"));
} elseif(isset($stmt2)) {
   $result = pg_prepare("myconnection", "my query", $stmt2);
   $result1 = pg_execute("myconnection","my query", array("0000","someCountry"));
} elseif(isset($stmt1)) {
   $result = pg_prepare("myconnection", "my query", $stmt1);
   $result1 = pg_execute("myconnection","my query", array("0000","someCity"));
} else {
   $result = pg_prepare("myconnection", "my query", $stmt);
   $result1 = pg_execute("myconnection","my query", array("0000"));
}

为了简便起见，我省略了所有错误检查（就像您一样）。

I omitted (just as you did) all the error checks for brevity.

这篇关于如何在PHP中制作动态的Postgres预准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！