Yii 1.1: Alternative wildcard characters CDbCriteria

2 followers

In SQL, wildcard characters can be used in "LIKE" expressions; the percent sign (%) matches zero or more characters, and underscore (_) a single character. Sometimes users are not familiar with these or they are using different ones like (*) and (+) similar to regular expressions. My colleages do so and then i decided to add this feature by extending CbCriteria. Especially adding a new parameter $wildcard_chars to the method addSearchCondition to replace them if needed with standard SQL characters:

class MyCDbCriteria extends CDbCriteria
{
/*
.....
*/  
public function addSearchCondition($column,$keyword,$escape=true,$operator='AND',$like='LIKE',$wildcard_chars = array('%'=>'%','_'=>'_'))
    {
    if($keyword=='') return $this;
    if($escape) 
    {
      $keyword='%'.strtr($keyword,array('%'=>'\%','_'=>'\_','\\'=>'\\\\')).'%';
    }
    else
    {
      $keyword=preg_replace(array("/".quotemeta($wildcard_chars['%'])."/","/".quotemeta($wildcard_chars['_'])."/"),array("%","_"),$keyword);
    }
    $condition=$column." $like ".self::PARAM_PREFIX.self::$paramCount;
    $this->params[self::PARAM_PREFIX.self::$paramCount++]=$keyword;
    return $this->addCondition($condition,$operator);
  }
/*
.....
*/
}

Therfore of course it is necessary to overwrite the method compare too:

public function compare($column, $value, $partialMatch=false, $operator='AND', $escape=true,$wildcard_chars = array('%'=>'%','_'=>'_'))
    {
        if(is_array($value))
        {
            if($value===array())
                return $this;
            return $this->addInCondition($column,$value,$operator);
        }
        else
            $value="$value";
 
        if(preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches))
        {
            $value=$matches[2];
            $op=$matches[1];
        }
        else
            $op='';
 
        if($value==='')
            return $this;
 
        if($partialMatch)
        {
            if($op==='')
                return $this->addSearchCondition($column,$value,$escape,$operator,'LIKE',$wildcard_chars);
            if($op==='<>')
                return $this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE',$wildcard_chars);
        }
        else if($op==='')
            $op='=';
 
        $this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator);
        $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;
 
        return $this;
    }

Now I can use my own very specail characters like '?' for any instead of '%'! This was my first article and i hope someone finds it useful.

Be the first person to leave a comment

Please to leave your comment.