CODEWITHSUNDEEP
phpsqlpostgresqlprepared-statementphp-pgsql
Fabrizio
Fabrizio

Reputation: 127

How to pass a set of values for IN clause to pg_execute() arrays

I pass to a function an array of categories and I want to make this query:

$sql = "SELECT * 
        FROM trans   
        WHERE id_user = $1 AND id_cat IN ($2)";

$value = array($id_user, implode(",", $categories));

$resource = pg_prepare($db, "get_trans", $sql);
$resource = pg_execute($db, "get_trans", $value);

But pg_execute gives me an error

Warning: pg_execute(): Query failed: ERRORE: sintassi di input non    valida per il tipo integer: "1,3,5,2,4,6,7" in /var/www/html/progetto-bd/application/function.php on line 370

Upvotes: 0

Views: 1566

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

An IN construct requires a row or a set, not an array.
If you pass an array, use an ANY construct.

SELECT * 
FROM   trans   
WHERE  id_user = $1 AND id_cat = ANY ($2);

Also, a Postgres array literal has the form '{elem1,elem2}'. (Note the curly braces.)

And you need to pass both parameters, like @toto21 already mentioned.

Upvotes: 3

toto21
toto21

Reputation: 612

You have two values distincts : $id_user (string) and list categories separate by comma (string after implode) your array $value should contain only two values. Try this :

$str_categories = implode(",", $categories);
$arr_value = array($id_user, $str_categories);
$resource = pg_prepare($db, "get_trans", $sql);
$resource = pg_execute($db, "get_trans", $arr_value);

Upvotes: 0

Related Questions