database search libraries

[eluser]MaxPar[/eluser]
I've been looking around for a helper or plugin that can help with database search (and handling how the results are processed). I'm aware of CI's Active Record class in the Database library, but I'm still going to wind up writing some kind of SQL query. I'm aware that (good) database searching is more than just something like:

Code:

$mysqlquery = 'SELECT * FROM `mytable` WHERE `body` LIKE \'%MYSTRING%\' ';

and to that end, I'm wondering if anyone is aware of pre-existing libraries that can be used to help search for particular patterns in a database. CI plugins are best- but unless they are extremely hidden on the wiki, I just can't find them! PHP code would also be alright, but then it might be annoying to get it work with CI.

[eluser]mrtopher[/eluser]
I'm not sure what your asking here. Wouldn't the patterns your looking for depend on your database structure? And if they rely on your database structure then I'm not sure how you would get around writing custom SQL queries.

The nice thing with CI Active Record is that, most of the time, you won't need to write actual SQL. You can write some pretty complex queries using just the Active Record functions.

[eluser]GSV Sleeper Service[/eluser]
there are a few options available for searching in mysql - see http://dev.mysql.com/doc/refman/5.0/en/f...earch.html for details.

[eluser]megabyte[/eluser]
Search is by far not something I am good at, but for fun I started putting something together a while ago in an attempt to learn and play. So feel free to share your negative input if my ideas are bad.

My idea was to use a temp table to combine both full text and 'like'

Code:

<?php

class Search extends Controller {

    var $tbl = '';
    var $temp_tbl = '';
    var $column = '';
    function Search()
    {
        parent::Controller();    
    }
    
    function index()
    {
        $this->column = 'search_column';
        $data['results'] = $this->get_results($this->input->post('keywords'));
        $this->load->view('search', $data);
    }
    
    function get_results($keywords){
        $keywords = trim($keywords);
        if($keywords != ''){
        $this->load->database();
        $this->load->library('table');
        $tmpl = array ('table_open' => '<table border="1" cellpadding="4" cellspacing="0">');                                                        
        $this->table->set_template($tmpl);
        $this->table->set_empty("&nbsp;");

        $this->tbl = "products";
        $this->temp_tbl = "myTempTable";
        //split keywords into 2 arrays one for short words and one for long
        $long_words = array();
        $short_words = array();
        
        $keywords = explode(' ', $keywords);
    
        foreach($keywords as $key =>$val){
            if (strlen($val) < 4){
            $short_words[] = $val;
            }
            else{
            $long_words[] = $val;
            }
        }
        
        // create the empty temporary table.
        $query = $this->db->query("drop table if exists $this->temp_tbl");
        $query = $this->db->query("CREATE TEMPORARY TABLE IF NOT EXISTS $this->temp_tbl like $this->tbl");
        // create the query for the long words
        if(count($long_words) != 0){
            foreach($long_words as $k =>$v){
            $query = $this->db->query("INSERT IGNORE INTO $this->temp_tbl (SELECT * FROM $this->tbl WHERE MATCH (style) AGAINST('$v'))");
            }
        }
        if(count($short_words) != 0){
        $sql = "INSERT IGNORE INTO $this->temp_tbl SELECT * FROM $this->tbl";
            foreach($short_words as $l => $m){
                if($l == 0){
                $sql .= " WHERE $this->column LIKE '%".$m."%'";
                }
                else{
                $sql .= " OR $this->column LIKE '%".$m."%'";
                }
            }
            $query = $this->db->query($sql);
        }
    
        $query = $this->db->query("Select DISTINCT COUNT(*) as occurences, id, style from $this->temp_tbl Group By id ORDER BY occurences DESC");
        //$query = $this->db->get('TPHS', 200, '');
                if ($query->num_rows() > 0)
                {
                $data['found'] =   'Results Found:'.$query->num_rows();
                $data['rows'] =  $this->table->generate($query);
                return $data;
                }
    
        
        }
}
    
}
?&gt;

[eluser]MaxPar[/eluser]
mrtopher: You're right, it's true that depending on how your database is structured, the search will be different. But as megabyte's post shows, there is still plenty of customization that can be done for a search- and it's that type of stuff that I'm trying to find a library for! It's been written a million times, and finding a library would be a good way to just get things done.

sleeper service: Yeah, I read through that link. I was hoping to find a plugin that has already implemented those searches with some level of abstraction.

In the same way that CI takes the drudgery out of coding repetitive parts of sites, I was hoping to find a plugin that could help take the drudgery out of putting together a nice search function.

[eluser]christian.schorn[/eluser]
I am working on a reverse index library for CI right now.

At the moment it is very coupled to the application (for reasons specific to the app), but I have already made preparations to turn the non-specific parts into a library/plugin. Should take 2-3 weeks, though.

[eluser]MaxPar[/eluser]
Cool. I'm looking forward to checking it out once you've posted it!