CODEWITHSUNDEEP
phpyii
shayward
shayward

Reputation: 427

MySQL Like with Array of strings

I have an array of words.

$terms = array('bars', 'bar', 'fun');

I am trying to find all records that contain each word.

TableName::model()->findAll('name LIKE :name, array(':name'=>implode(',', $terms)));

Not sure the right way to find all within the array.

Upvotes: 2

Views: 693

Answers (2)

topher
topher

Reputation: 14860

Manquer's answer is correct for your specific situation. However, due to the implementation of compare (read the source Luke) it will not work for partial searches i.e LIKE '%ADF%'. For this you can loop through the array and add each element using compare:

$c1=new CDbCriteria;

foreach($terms as $txt){ 
    $c1->compare('name',$txt,true,'OR');
}

TableName::model()->findAll($c1);

However should you run into a scenario where you need to add another condition for another group the results may not be as expected due to the way compare creates the query i.e you may end up with

WHERE ((((type=1) OR (name LIKE '%ABC%')) OR (name LIKE '%RET%')) OR (name LIKE '%Anything else%'))

To avoid this you have a couple of options:

  1. Always ensure your "array condition" appears first.

  2. Create another criteria object specifically for your array values and merge this into the other object using CDbCriteria::merge():

    $c1=new CDbCriteria; // original criteria
$c2=new CDbCriteria; // criteria for handling our search values

foreach($terms as $txt){ 
    $c2->compare('name',$txt,true,'OR');
}

$c1->mergeWith($c2); // Merge $c2 into $c1

  3. Create another criteria object specifically for your array values and manually add this condition into the other object using CDbCriteria::addCondition():

    $c1=new CDbCriteria; // original criteria
$c2=new CDbCriteria; // criteria for handling our search values

foreach($terms as $txt){ 
    $c2->compare('name',$txt,true,'OR');
}

$c1->addCondition($c->condition); // Merge $c2 into $c1
$c1->params+=$c2->params; // Add the params for $c2 into $c1

Use (2).

Reference: http://www.yiiframework.com/wiki/610/how-to-create-a-criteria-condition-for-a-multiple-value-text-search-i-e-a-like-in/

Upvotes: 1

Manquer
Manquer

Reputation: 7647

You can use CDbCriteria class for this sort of Query in findAll like this

$terms = array('bars', 'bar', 'fun');
$criteria = new CDbCriteria 
$criteria->compare('name',$terms,true,"AND",true);

TableName::model()->findAll($criteria);

see compare documentation for details

Upvotes: 1

Related Questions