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
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
"<>hello", the criteria will find the rows where the content is
NOT LIKE '%hello%'.
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: