J4 How to bind string for IN clause?

22 views
Skip to first unread message

pioa...@gmail.com

unread,
Oct 19, 2021, 7:49:54 AM10/19/21
to Joomla! General Development
How to bind string for IN clause?
This code doesn't work:
$field_ids = implode(',', $array_tmp);
...
->where($db->qn('field_id') .' IN (:field_ids)')
...
->bind(':field_ids', $field_ids, ParameterType::STRING)
I will be grateful for information on how to do it properly.
Peter

Roger Creagh

unread,
Oct 19, 2021, 2:35:30 PM10/19/21
to joomla-de...@googlegroups.com
On Tue, 2021-10-19 at 04:49 -0700, pioa...@gmail.com wrote:
>where($db->qn('field_id') .' IN (:field_ids)')

why not just do

 ->where('field_id IN ('.implode(',', $array_tmp).')');

Works in J3 so should work in J4 

Chris Davenport

unread,
Oct 19, 2021, 3:33:44 PM10/19/21
to Joomla! General Development
If the $array_tmp are strings they will need individually quoting, so you would need to use something like this:

->where('field_id IN (' . implode(',', array_map([$db, 'quote'], $array_tmp)) . ')')

Chris.

pioa...@gmail.com

unread,
Oct 21, 2021, 3:11:16 AM10/21/21
to Joomla! General Development
It works fine in "standard" quering. But Joomla developers are uses now  Prepared Statemens.
They works fine except IN clause. After few tests it looks like $field_ids is quoted somewhere during data bind or query execution and returns only record for first ID (cropped after first comma).
I will put this on Issue tracker.
Thanks for help!

Cliff

unread,
Oct 21, 2021, 11:49:41 AM10/21/21
to Joomla! General Development
->whereIn($db->quoteName('field_id'), $array, ParameterType::STRING) is used for this sort of thing - leave out the last variable if the array is integers.

Note: whereIn rather than where

I sent this message to the list by email previously. Does that not work any more?

Cliff Ford

unread,
Oct 21, 2021, 4:21:57 PM10/21/21
to joomla-de...@googlegroups.com
->whereIn('field', $array)

But best to look up the whereIn call.

On 19/10/2021 20:33, 'Chris Davenport' via Joomla! General Development
wrote:
> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-gene...@googlegroups.com
> <mailto:joomla-dev-gene...@googlegroups.com>.
> To view this discussion on the web, visit
> https://groups.google.com/d/msgid/joomla-dev-general/03ea1949-66b5-4df9-8cb0-6b6273fd44fcn%40googlegroups.com
> <https://groups.google.com/d/msgid/joomla-dev-general/03ea1949-66b5-4df9-8cb0-6b6273fd44fcn%40googlegroups.com?utm_medium=email&utm_source=footer>.
Reply all
Reply to author
Forward
0 new messages