CODEWITHSUNDEEP
phppostgresqlpdoprepared-statement
Hikari
Hikari

Reputation: 3947

Custom where clauses in PHP prepared statement

I'm reading about PDO and prepared statement, focused in Postgres.

As I understood how ps is implemented in PHP, I can register a named query, then define variables for it and run it multiple times. But what happens when the use of where clauses depend on parameters.

For exemple, if !empty($param3) then I add a where clause, but if it is empty that clause won't be used in the query.

The best situation for this is a filter form to narrow a list of records: if no filter is used the query won't even use WHERE, while all filters may be used resulting in a big WHERE query.

As I see now, I'd need to register 1 prepared statement for each situation, but that would result in lots of statements to maintain and test separately.

Upvotes: 2

Views: 509

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Don't prepare queries you aren't going to execute.

It's true that you need to prepare a different SQL string if you have differences in the search terms in the WHERE clause.

Prepared queries allow you to use parameters in place of literal values only; that is, where you would normally put a quoted string or date, or a numeric literal. But parameters cannot be used for other syntax, like table names, column names, SQL expresions, SQL keywords, etc.

So you must create the SQL string before preparing it, depending on application conditions like !empty($param3).

Upvotes: 1

Oswald
Oswald

Reputation: 31647

If you want to use prepared statements, register 1 prepared statement for each situation.

Upvotes: 2

Related Questions