Fulltext search, is there a better way to do this?

[eluser]bugboy[/eluser]
Hi all i need to make a custom search function

I have 3 areas (blog, news, page) to search each area consisting of two tables.

One table contains the title and if its published or not and the the other consists of the actual data, the body text and such.

so far my model looks like this and works great. I'm just wondering if there is a better way to do this though.

Code:

function quick_search($search = NULL)
    {
        // capture results and put them into a over all array
        $results = array();
        // news fulltext search
        $string = $this->_user.'.username as username,'.$this->_user.'.id as user_id,'.$this->_news.'.*, '.$this->_news_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_news);
        $this->db->join($this->_user, "$this->_news.author = $this->_user.id", 'left');
        $this->db->join($this->_news_data, "$this->_news.id = $this->_news_data.news", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['news'] = $query->result();
        }
        
        // blog fulltext search
        $string = $this->_user.'.username as username,'.$this->_user.'.id as user_id,'.$this->_blog.'.*, '.$this->_blog_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_blog);
        $this->db->join($this->_user, "$this->_blog.author = $this->_user.id", 'left');
        $this->db->join($this->_blog_data, "$this->_blog.id = $this->_blog_data.blog", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['blog'] = $query->result();
        }
        
        // page fulltext search
        $string = $this->_page.'.*, '.$this->_page_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_page);
        $this->db->join($this->_page_data, "$this->_page.id = $this->_page_data.page", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['page'] = $query->result();
        }
                
        return $results;
        
    }

Any advice would be great.

So far my memory usage on this page is (does include other db calls and stuff though) 2,339,528 bytes
Other stats are

Loading Time Base Classes :0.0165
Controller Execution Time ( Search / Index ):0.0476
Total Execution Time:0.0642

[eluser]adamp1[/eluser]
Take a look at Spinx for better SQL searching. http://sphinxsearch.com/about.html

Its far better than full-text search in normal mySQL

[eluser]bugboy[/eluser]
I don't really want to start using another third party tool at this stage for this.

However that looks great and i'll be looking into that at some stage for another project.
Thanks for link.

[eluser]bluepicaso[/eluser]
[quote author="bugboy" date="1251123732"]Hi all i need to make a custom search function

I have 3 areas (blog, news, page) to search each area consisting of two tables.

One table contains the title and if its published or not and the the other consists of the actual data, the body text and such.

so far my model looks like this and works great. I'm just wondering if there is a better way to do this though.

Code:

function quick_search($search = NULL)
    {
        // capture results and put them into a over all array
        $results = array();
        // news fulltext search
        $string = $this->_user.'.username as username,'.$this->_user.'.id as user_id,'.$this->_news.'.*, '.$this->_news_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_news);
        $this->db->join($this->_user, "$this->_news.author = $this->_user.id", 'left');
        $this->db->join($this->_news_data, "$this->_news.id = $this->_news_data.news", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['news'] = $query->result();
        }
        
        // blog fulltext search
        $string = $this->_user.'.username as username,'.$this->_user.'.id as user_id,'.$this->_blog.'.*, '.$this->_blog_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_blog);
        $this->db->join($this->_user, "$this->_blog.author = $this->_user.id", 'left');
        $this->db->join($this->_blog_data, "$this->_blog.id = $this->_blog_data.blog", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['blog'] = $query->result();
        }
        
        // page fulltext search
        $string = $this->_page.'.*, '.$this->_page_data.'.*';
        $this->db->select($string);
        $this->db->from($this->_page);
        $this->db->join($this->_page_data, "$this->_page.id = $this->_page_data.page", 'left');
        $this->db->where('MATCH (dscpn, text, keyword) AGAINST ("'.$search.'") OR MATCH (title) AGAINST("'.$search.'")', NULL, FALSE);
        $this->db->where('published', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results['page'] = $query->result();
        }
                
        return $results;
        
    }

Any advice would be great.

So far my memory usage on this page is (does include other db calls and stuff though) 2,339,528 bytes
Other stats are

Loading Time Base Classes :0.0165
Controller Execution Time ( Search / Index ):0.0476
Total Execution Time:0.0642[/quote]

The question i have is, how you do put pagination to it, with LIMIT keyword?

[eluser]dickfu[/eluser]
sphinx is a better way than normal mysql