CODEWITHSUNDEEP
phpmysqlyii2
user3007294
user3007294

Reputation: 951

Dynamically set param in SQL statement via Expression

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)

Upvotes: 1

Views: 728

Answers (2)

Pitwas Patel
Pitwas Patel

Reputation: 9

hi there i have found very easy solution for above issues

$key = $_POST['question'];
$val = $_POST['answer'];
$da = [$key=>$val];
$d=json_encode($da);
$q = Yii::$app->db->createCommand("SELECT data from form_submission WHERE 
JSON_CONTAINS(data,'$d') and form_id=$id")->queryAll();

Upvotes: 0

rob006
rob006

Reputation: 22174

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;
};

Upvotes: 2

Related Questions