Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.0k views
in Technique[技术] by (71.8m points)

postgresql - How to make dynamic postgres prepared statements in 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"));

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...