Thread: Prepared statements with a variable number of parameters

I'm using PDO in PHP for database access (actually Zend Framework), and
following best practice by using parameters in prepared statements.

My question is, what do people normally do when there's a variable
number of parameters? - for example:

  select ..... where item_id in ( ... );

In this case, I mightn't know how many values will go inside the
parentheses.

Thanks,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Hello

2009/10/10 Raymond O'Donnell <rod@iol.ie>:
> I'm using PDO in PHP for database access (actually Zend Framework), and
> following best practice by using parameters in prepared statements.
>
> My question is, what do people normally do when there's a variable
> number of parameters? - for example:
>
>  select ..... where item_id in ( ... );
>

You can use arrays here.

select ... where item_id = ANY(string_to_array($1, ','))

Regards
Pavel Stehule

> In this case, I mightn't know how many values will go inside the
> parentheses.
>
> Thanks,
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On 10/10/2009 18:52, Pavel Stehule wrote:

> 2009/10/10 Raymond O'Donnell <rod@iol.ie>:

>> My question is, what do people normally do when there's a variable
>> number of parameters? - for example:
>>
>>  select ..... where item_id in ( ... );
>>
>
> You can use arrays here.
>
> select ... where item_id = ANY(string_to_array($1, ','))

Nice one! Thanks Pavel.

My item_id values are integers, so I added ::integer[] to the above and
all works nicely:

    ...where item_id = any(string_to_array($1, ',')::integer[])

Thanks again,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------