Dynamically set param in SQL statement via Expression

Question

I am currently using the Yii2 framework (specifically https://www.yiiframework.com/doc/api/2.0/yii-db-expression)

I am running into an issue where I can not dynamically set a param while creating multiple Expression statements.

$citiesArray = explode("Chicago", "New York", "Dallas");

foreach ($citiesArray as $index => $city) {
  $expression = new Expression(
     'JSON_CONTAINS(
         field_location_addressLocation,
         :city,
         \'$.parts.city\'
     )',
     [':city' => json_encode($city)]
  );
  array_push($cityExpressions, $expression);
};

The problem here is that :city is simply replaced each time the iteration goes through and ultimately, the SQL statement translates to only caring about the last city passed (in this case, "Dallas").

I have tried incorporating the index of loop to create a unique value, but had no such luck:

foreach ($citiesArray as $index => $city) {
   $expression = new Expression(
      'JSON_CONTAINS(
         field_location_addressLocation,
         \':city-$index\',
         \'$.parts.city\'
      )',
     [':city-'.$index => json_encode($city)]
   );
   array_push($cityExpressions, $expression);
};

If this even possible? I believe this is more a mySQL (MariaDB) issue than it is a Yii since ultimately these expressions are converted to SQL statements.

Any insight would be greatly appreciated.

(I am using the param documentation shown here: https://www.yiiframework.com/doc/api/2.0/yii-db-expression#$params-detail)

Answer 1

You're right about replacing parameters - parameters names should be unique. But you're incorrectly building your expression string - variables interpolation will work only on strings inside of ". If you use ', then $index will not be treated as value inside of $index variable, but as literal string $index. Try this:

foreach ($citiesArray as $index => $city) {
   $expression = new Expression(
      "JSON_CONTAINS(
         field_location_addressLocation,
         ':city$index',
         '\$.parts.city'
      )",
      [':city' . $index => json_encode($city)]
   );
   $cityExpressions[] = $expression;
};