Difference between #1 and #4 of
Creating a parameterized LIKE query

Revision #4 has been created by François Gannaz on Jun 2, 2011, 7:30:55 PM with the memo:

Replace compare() with addSearchCondition()
« previous (#1) next (#5) »

Changes

Title unchanged

Creating a parameterized LIKE query

Category unchanged

FAQs

Yii version unchanged

Tags unchanged

LIKE, substring query

Content changed

[...]
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**:

```php
$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!
```
TJust as in any other prepared statement, the `:match` in the condition should **not** havebe surrounded by quotes - Yii handles this parameter binding properly (adding the quotes will make it find nothing). Alternate versions: ------------------- ```php $match = addcslashes($match, '%_'); // escape LIKE's special characters
 
 
// directly into findAll()
$comments = Comments::model()->findAll(
'content LIKE :match',
[...]
) );
```

 
Better: use CDbCriteria::compareaddSearchCondition() ---------------------------------- If the query uses a direct `CDbCriteria` object, it's more efficient (and just as safe) to do a simple substring match with the `compare[CDbCriteria::addSearchCondition()`] method, which handles it all directly. ```php $q = new CDbCriteria(); $q->compare('content', $match, true);    // true=partial match
 
 
$comments = Comments::model()->findAll( $q );
 
```
 
The third parameter of `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)` asks for a **partial match**, which Yii turns into a LIKE query using `%` wildcards before and after the match variablebut 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.
 
For instance, the [DGSphinxSearch Extension](http://www.yiiframework.com/extension/dgsphinxsearch) shows how to integrate [Sphinx Search](http://sphinxsearch.com/) into Yii.
13 0
21 followers
Viewed: 144 661 times
Version: 1.1
Category: FAQs
Tags: LIKE, substring query
Written by: Steve Friedl
Last updated by: Asgaroth
Created on: May 29, 2011
Last updated: 13 years ago
Update Article
Revert to #1 | Revert to #4

Revisions

View all history