How to bind a dynamic list to a WHERE IN query

Question

I have below query that has an IN condition to which I want to bind a dynamic list of department codes instead of passing in the codes directly in the query as I am doing it now. How do I do this?

<?php

  $params = [
            ':academicYear' => $academicYear
        ];

  $sql = "SELECT 
        MUTHONI.COURSES.COURSE_CODE, MUTHONI.COURSES.COURSE_CODE
        FROM MUTHONI.MARKSHEET_DEF 
        INNER JOIN MUTHONI.COURSES ON MUTHONI.MARKSHEET_DEF.COURSE_ID = 
        MUTHONI.COURSES.COURSE_ID
        WHERE MUTHONI.MARKSHEET_DEF.MRKSHEET_ID LIKE CONCAT(:academicYear, '%')
        AND MUTHONI.COURSES.DEPT_CODE IN ('B16','B17','B18','B19','B20','B21')";

  $connection->createCommand($sql)->bindValues($params)->queryScalar();

Make an array, for example `$codes = array('B16','B17','B18','B19','B20','B21')` and pass it to the `IN ($codes)`. — Mainul Hasan, Oct 16 '21 at 10:03
@MainulHasan And face SQL injection issue. Instead it would be better to build as much number of `?` as there is number of items in the array and then use general prepared statement. Of bind some collection variable. — astentx, Oct 16 '21 at 10:33
Does this answer your question? [Can I bind an array to an IN() condition?](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) — astentx, Oct 16 '21 at 10:35
Unfortunately you will have to bind each code separately. Of course you can make use of `foreach` and `implode` to make it independent of number of codes. — Furgas, Oct 16 '21 at 10:05

score 0 · Answer 1 · answered Oct 19 '21 at 12:28

You can use query builder. It will resolve the parameter binding for you.

$codes = ['B16','B17','B18','B19','B20','B21'];
$query = (new \yii\db\Query())
    ->select(['MUTHONI.COURSES.COURSE_CODE', 'MUTHONI.COURSES.COURSE_CODE'])
    ->from('MUTHONI.MARKSHEET_DEF')
    ->innerJoin(
        'MUTHONI.COURSES',
        'MUTHONI.MARKSHEET_DEF.COURSE_ID = MUTHONI.COURSES.COURSE_ID'
    )->where(
        new \yii\db\Expression(
            "MUTHONI.MARKSHEET_DEF.MRKSHEET_ID LIKE CONCAT(:academicYear, '%')",
             [':academicYear' => $academicYear]
        )
    )->andWhere(['in', 'MUTHONI.COURSES.DEPT_CODE', $codes]);

$result = $query->scalar();

The query builder will create a param and bind the value for each item in $codes array for you.

You can also use the alternative syntax:

andWhere(['MUTHONI.COURSES.DEPT_CODE' => $codes])

PS: I haven't ever work with Oracle in Yii so I'm not sure if the table/column names are written properly. The query builder works same way for every DBMS.

How to bind a dynamic list to a WHERE IN query

1 Answers1