Brett Brett - 6 months ago 49
PHP Question

Correct way to bind parameters using MySQL "IN" syntax in Yii2?

Ok, I'm using

Yii2
and I'm familiar with preparing/binding data when using mysql queries, such as:

$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id=:some_id");
$sql->bindValue(':some_id', $some_id);


But what about when the value may contain multiple values, such as when using the
MySQL
syntax
IN
?

For example:

$sql = $this->db->createCommand("UPDATE some_table SET something='foo' WHERE some_id IN (:parents)");
$sql->bindValue(':parents', $parents);


Now as I understand the above would only work well if the
$parents
var only had one value; but if it had multiple values such as
1,2,3
then you would end up with something like
'1,2,3'
when you really want
'1','2','3'
OR
1,2,3
.

What is the correct way to do this?

Answer

I ended up doing it like this:

$parents_safe = '';
$parents_sep = explode(',', $parents);

foreach ($parents_sep as $parent) {
    $parents_safe .= $this->db->quoteValue($parent) . ',';
}

$parents_safe = rtrim($parents_safe, ',');

Where $this->db is an instance of Yii::$app->db.

Comments