How to ignore question mark as placeholder when using PDO with PostgreSQL

user_434792 0
Viewed 1879

Note:

This question can be considered as duplicate of this Question. It does point to the same problem with PDO. But its workaround solution is a bit different as the target differ. I will post there the workaround for JSONB and the link to the PHP ticket.

When I prepare the following query:

SELECT * FROM post WHERE locations ? :location;

The following warning occur:

Warning: PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line xx

The question mark is an valid PostgreSQL operator but PDO condsider it as a placeholder.

Is there a proper way to configure PDO to ignore question mark as placeholders?

I will post a workaround bellow. Hoping there is a better way

Edit I add a ticket at PHP bug tracing system

pdo postgresql php
7 Answers

Since PHP 7.4, supports for escaping question mark have landed.

(...) question marks can be escaped by doubling them (...). That means that the “??” string would be translated to “?” when sending the query to the database, whereas “?” is still going to be interpreted as a positional parameter placeholder.

In your example, you can use:

$sql = "SELECT * FROM post WHERE locations ?? :location;";
user_15722360

You can use

  • jsonb_exists instead of ?
  • jsonb_exists_any instead of ?|
  • jsonb_exists_all instead of ?&

But there is no documentions on postgresql site.

user_6641570

For searching keys and according to Yoann answer, I have tested that the expression ( jsonbData ? 'keySearched' ) is equivalent to jsonb_exists(jsonbData , 'keySearched')

user_85929110

Use CREATE OPERATOR ~@& (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_any) and use ~@& instead ?| everything will works fine

user_78737850

This works for me:

jsonb_exists(some_jsonb_array,'search_value');
user_50413940
Related