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

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.

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.