Cakephp Search for MySQL

Question

Which implementation of a text based search for a Cakephp application which uses a MySQL database is "best"?

Answer 1

I always tended to make a 'Search' model, 'SearchesController' and views, then write my own custom script to search my database using,

$this->Search->query("SELECT * FROM table WHERE condition 1 AND condition 2");

I am not aware of any "best" search, but there may well be components and plugins which will do something like this for you. Best to check the Bakery http://bakery.cakephp.org/

Answer 2

Sphinx is one of the most powerful SQL text search engines - http://sphinxsearch.com/

There is a CakePHP Behaviour written up at the bakery: http://bakery.cakephp.org/articles/view/sphinx-behavior

The thing to note is that Sphinx has several components and some need to run as daemons on your machine (similar to having apache or mysql processes running). Also you need to "index" your database every so often to keep results fresh. It can be daunting to setup at first, but definitely worth the effort if you have a lot of records and big chunks of text to search through.

Answer 3

How I implemented Search for Posts:

The search form code:

<?php
echo $form->create('Deal',array('action' => 'search'));
echo $form->input('Deal.search');
echo $form->end('Search');
?> 

In the Controller, put the following Search function:

function search()
{
    if (!empty($this->data)) {
        $searchstr = $this->data['Post']['search'];
        $this->set('searchstring', $this->data['Post']['search']);
        $conditions = array(
            'conditions' => array(
            'or' => array(
                "Post.title LIKE" => "%$searchstr%",
                "Post.description LIKE" => "%$searchstr%"
            )
            )
        );
        $this->set('posts', $this->Post->find('all', $conditions));
    }
} 

The view code:

<?php foreach ($posts as $post): ?>
    <tr>
        <td><?php echo $post['Post']['id']; ?></td>
        <td>
            <?php echo
$html->link($post['Post']['title'],'/posts/view/'.$post['Post']['id']);?>
                </td>
        <td><?php echo $post['Post']['created']; ?></td>
    </tr>
<?php endforeach; ?> 

May not be the best/elegant, but works well for simple queries.