Cdb Criteria Compare And Mysql Like

Yii 1.1 General Discussion
1

Hi All,

I have a problem, searched on net but did not got the near by solution.

The data in Mysql DB contains

1 TADANO GR-1000XL-2

2 2008 TEREX RT-780

3 2007 KOBELCO CK-1000III

when user search for any item form above 3 without hyphen ‘-’ and wit or without spaces it wont display any result. Eg ‘TADANO GR 1000XL 2’ or ‘TADANO GR1000XL2’.

here is my compare $criteria->compare(‘title’,$this->title,true, ‘OR’);

I found that it could be fixed with

SELECT * FROM product_table WHERE REPLACE(’".$keyword."’,’-’,’’) LIKE REPLACE(product_name,’-’,’’)

or using soundex but I want use search() method formate generated by Yii.

Please let me know how I may modify this to select data with dashes in it.

Thanks in advance

2

You might also take a look at mysql fulltext index functionality. With that mysql is better at searching partial matches.

Http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

3

Fulltext is great if your tables are MyISAM, but useless if you are using InnoDB.

You might consider trying this…

Create another field in the table called searchable or something. Use beforeSave in your model to strip the field of spaces and dashes (and other non-desireables) and search that field as well. A bit of a clunky solution, but it might work if fulltext is not a viable option.

4

Ya here you are right, My tables are innoDB will try with the search field option.