Yii 1.1: Creating a parameterized LIKE query

24 followers

It's common to see users wishing to make substring DB queries, using the % metacharacter used to match anything; in this FAQ we'll search the tbl_comments table from the blog tutorial hoping to find the text in $match in the content column

A frequently seen yet incorrect effort:

$q = new CDbCriteria( array(
    'condition' => "content LIKE '%:match%'",      // DON'T do it this way!
    'params'    => array(':match' => $match)
) );
 
$comments = Comments::model()->findAll( $q );     // finds nothing

fails to find anything. And the version without single quotes around the :match yields an SQL error.

This search is properly achieved by moving the % metacharacters into the bound parameter:

$match = addcslashes($match, '%_'); // escape LIKE's special characters
$q = new CDbCriteria( array(
    'condition' => "content LIKE :match",         // no quotes around :match
    'params'    => array(':match' => "%$match%")  // Aha! Wildcards go here
) );
 
$comments = Comments::model()->findAll( $q );     // works!

Just as in any other prepared statement, the :match in the condition should not be surrounded by quotes - Yii handles this parameter binding properly.

Alternate versions:

$match = addcslashes($match, '%_'); // escape LIKE's special characters
 
// directly into findAll()
$comments = Comments::model()->findAll(
    'content LIKE :match',
    array(':match' => "%$match%")
);
 
// merging in a parameterized scope:
$this->getDbCriteria()->mergeWith( array(
    'condition' => 'content LIKE :match',
    'params'    => array(':match' => "%$match%")
) );

Better: use CDbCriteria::addSearchCondition()

If the query uses a direct CDbCriteria object, it's more efficient (and just as safe) to do a simple substring match with the CDbCriteria::addSearchCondition() method, which handles it all directly.

$q = new CDbCriteria();
$q->addSearchCondition('content', $match);
 
$comments = Comments::model()->findAll( $q );

Yii will turn this criteria into a LIKE query using % wildcards before and after the match variable. By default, it protects active characters in the variable.

One could also use $q->compare('content', $match, true) but it has a few side effects. For instance, if $match is "<>hello", the criteria will find the rows where the content is NOT LIKE '%hello%'.

Warning

The pattern LIKE '%XXX%' must not be abused.

  • This is not scalable and can be really slow on medium-sized databases.
    For instance, MySQL cannot use indexes with such criteria, so it has to process the whole data.
  • This is not a google-like search, and complex queries like zend -framework "php core" are not possible. Users often expect more than just a substring search.

While both Postgresql and MySQL include an embedded full-text search, this kind of search is often handled by another service. The most well-known free systems are Xapian, Sphinx and Lucene.

There are already some yii extensions for this:

Total 6 comments

#15801 report it
namedin at 2013/12/18 09:09am
Tnx :) CONCAT is another way

Another way to have the LIKE expressions (especially RLIKE(REGEXP)) within the long Queries is to CONCAT

$db->
    createCommand("SELECT ... WHERE ... LIKE CONCAT('%', :param1, '%', :param2) ESCAPE '='")
    ->query(array(
        ':param1' => YourTools::EscapeLikeWildCards($thisModel->attribute1, '='),
        ':param2' => YourTools::EscapeLikeWildCards($thisModel->attribute2, '='),
    ))
#15611 report it
Francis Rodrgues at 2013/11/28 06:09am
php pdo prepare repetitive variables

Using $ this-> params in the query must be unique for each condition, as shown below:

http://stackoverflow.com/a/7604080

#13654 report it
zigi at 2013/06/14 07:08am
Warning

Since v5.11.6 MySQL includes indexes and especially FULLTEXT search

#10435 report it
bonnie at 2012/10/27 06:48pm
Thanks for this guidance

I have a question how can I perform a search with a string match like this Manhattan, NY Thanks

#9723 report it
Rahmat Awaludin at 2012/09/06 09:57am
thanks!

Thanks, it save my day :D

#4026 report it
samdark at 2011/05/30 09:43am
Another variant using CDbCriteria

Leave a comment

Please to leave your comment.

Write new article