[SOLVED] Advanced search in Doctrine 1

Advanced search in Doctrine 1

I am using LIKE query in DOCTRINE1 to search keywords in DB. Here is the example : I have a string available in DB "Developer in the hell"

$str = "Developer in the hell";
$srchKey = "%" . mysql_real_escape_string($searchString) . "%";
$q = Doctrine_Query::create()->from("Movie as m")
                             ->where('m.name LIKE '."'$srchKey'". ' or m.keywords LIKE'."'$srchKey'")
                             ->fetchArray();

The concern is if I search for "Developer" it returns me the result but if I search for "Developer hell" is returns me nothing. Because middle to words from the string are skipped.

Is there any wild card/advance options/conditions that DOCTRINE provide to handle such case.

Solution

First of all, you're doing it wrong when building the query.

Doctrine escape parameter that you give to the query, so you don't have to use mysql_real_escape_string on your own.

$str = "Developer in the hell";
$srchKey = "%" . $str . "%";
$q = Doctrine_Query::create()
    ->from("Movie as m")
    ->where('m.name LIKE ? or m.keywords LIKE ?', array($srchKey, $srchKey))
    ->fetchArray();

About your search engine, you should have a look at the Searchable behavior. It will be more powerfull than a simple LIKE query. But I don't know if it handle multiple term search like you want.

Or you can give a try to Lucene. A tutorial is available as part of the Jobeet tutorials.