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:
Another variant using CDbCriteria
Thanks, it save my day :D
Thanks for this guidance
I have a question how can I perform a search with a string match like this Manhattan, NY
Since v5.11.6 MySQL includes indexes and especially FULLTEXT search
php pdo prepare repetitive variables
Using $ this-> params in the query must be unique for each condition, as shown below:
Tnx :) CONCAT is another way
Another way to have the LIKE expressions (especially RLIKE(REGEXP)) within the long Queries is to CONCAT
If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.