postgresql - How to make dynamic postgres prepared statements in PHP

Question

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"));

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!

Thank you in advance!

Answer 1

In a prepared statement, the SQL is static on purpose. The number of parameters cannot vary once the statement is prepared.

But it'd be easy for your code to submit the right number of parameters depending on the statement. You could add a variable for the counter of parameters, and a dynamic php array to pass to pg_execute instead of hard-coded literals. And they would be incremented/populated inside the if (isset(...)) branches.