CGridView - FULLTEXT Search

Searched for this but could not find it anywhere.

I have a CGridView and one of the columns I would like to be able to use

MySQL FULLTEXT search capability.

Has anybody done this or know how to?

In your model’s search() method, replace the line




$critera->compare('mycol', $this->mycol, true);



with




$expr = self::escapeFulltextExpr($this->mycol);

$critera->addCondition("MATCH (mycol) AGAINST ('{$expr}' IN BOOLEAN MODE)";



I guess you need to write the method escapeFulltextExpr() yourself. It should not only use CDbConnection’s quote method or addslashes(), but also escape or remove the operators you don’t want to use. See http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html for the list of these operators.

BTW, I have already used CGridView for full-text search, but it was not with MySQL. Last time I checked, MySQL’s full-text engine was quite slow, and when not in boolean mode the relevancy was very poor. Moreover, it only worked on MyISAM, which was a big no-no. I know InnoDB supports it since a few weeks, but I won’t test it. I used a dedicated search service that work with MySQL: Sphinx Search is excellent, and there is an extension to use it with Yii.

try this - it works!




    public function search() {

        $criteria = new CDbCriteria;


        $criteria->compare('code', $this->code, true);


        if ($this->description) {

            $params = explode(' ', $this->description);

            foreach ($params as $param)

                $criteria->addCondition("MATCH (description) AGAINST ('{$param}' IN BOOLEAN MODE)");

        }


        return new CActiveDataProvider($this, array(

                    'criteria' => $criteria,

                ));

    }



If I filter description for ‘keyword1’ - it gives me description containing keyword1

If I filter description for ‘keyword1 keyword2’ - it gives me description containing keyword1 and keyword2

If I filter on code=7200 and description= ‘keyword1 keyword2’ - it gives me description containing keyword1 and keyword2, and code containing 7200

No, don’t try it, you’ll be sql injected !

SQL injected How and How to avoid in this scenario.

I use the input extension to purify anything that could be dangerous:

http://www.yiiframework.com/extension/input/

the input extension helps you avoid xss exploits, but does not protect you against sql injections, because yii has already covered that.

Always use param binding feature that yii offers to avoid sql injection.




public function search() {

	$criteria = new CDbCriteria;


	$criteria->compare('code', $this->code, true);


	if ($this->description) {

		$unsafe=explode(' ', $this->description);

		$i=0;

		$params=array();

		foreach($unsafe as $notSafe)

		{

		   ++$i;

		   $criteria->addCondition("MATCH (description) AGAINST (<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' />.".$i." IN BOOLEAN MODE)");

		   $params['<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' />'.$i]=$notSafe;

		}

		$criteria->params=$params;

	}


	return new CActiveDataProvider($this, array(

		'criteria' => $criteria,

	));

}



I don’t believe you are familiar with the extension. It covers all.

i wrote it.

[color="#1C2837"][size="3"]Agree with Twisted1919 : Thanks buddy - sometime we forget the basics. [/size][/color]