CODEWITHSUNDEEP
phppostgresqlpdo
Michael Ben-Nes
Michael Ben-Nes

Reputation: 7645

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

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

Upvotes: 11

Views: 2646

Answers (7)

Julien Fastré
Julien Fastré

Reputation: 1048

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

Upvotes: 6

P E Megaard
P E Megaard

Reputation: 11

This works for me:

jsonb_exists(some_jsonb_array,'search_value');

Upvotes: 0

Levon Babayan
Levon Babayan

Reputation: 282

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

Upvotes: 0

Julian Arias
Julian Arias

Reputation: 1

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

Upvotes: 0

Yoann
Yoann

Reputation: 5077

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.

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

Ok, the simplest way is to create the new operator with the same options, like:

-- Operator: ~!@#%^&(jsonb, text)

-- DROP OPERATOR ~!@#%^&(jsonb, text);

CREATE OPERATOR 
  ~!@#%^& -- Change it to any other non-conflicted symbols combination
(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);
COMMENT ON OPERATOR ~!@#%^&(jsonb, text) IS 'key exists';

(Original script was generated by pgAdmin)

And use it in usual way like

SELECT * FROM post WHERE locations ~!@#%^& :location;

Upvotes: 1

Michael Ben-Nes
Michael Ben-Nes

Reputation: 7645

This is a workaround to my problem. It solve the problem by using PostgreSQL function to replace the ? operator.

I don't really like it because it does not make PDO more compliance to PostgreSQL. But I found no real solution.

CREATE FUNCTION json_key_exists(JSONB,TEXT) RETURNS BOOLEAN LANGUAGE SQL STABLE AS $f$
    SELECT $1 ? $2
$f$;

And now I can use the query:

SELECT * FROM post WHERE json_key_exists(locations, :location);

The workaround was suggested by the fabulous RhodiumToad from freenode #postgresql

Edit

As @Abelisto suggested, there is no need to create the function above as jsonb_exists(jsonb, text) is avialabe

Upvotes: 6

Related Questions