Problems with a Search Function

[eluser]Brokenegg.wb[/eluser]
ok, so i'm creating an e-commerce site for intaglio seals, i have everything pretty much hammerd out except the function that allows the user to search for spicific items, heres what i have so far:

in the Controller:

Code:

function search()
    {
        $this->load->library('pagination');
        $this->load->library('table');

        $config['base_url'] = 'http://www.rareintaglio.com/index.php/store/search';
        $config['total_rows'] = $this->db->get('items')->num_rows();
        $config['per_page'] = 12;
        $config['num_links'] = 7;
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';
        $this->pagination->initialize($config);
        
        
        $data['result'] = $this->site_db_upload->get_records_store_search();
        
        
        $this->load->view('test', $data);
    }

site_db_upload:
[

Code:

function get_records_store_search()
    {
        /*$config['base_url'] = 'http://www.rareintaglio.com/index.php/store';
        $config['total_rows'] = $this->db->get('items')->num_rows();
        $config['per_page'] = 9;
        $config['num_links'] = 7;
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';
        
        $this->db->select('item_name, attribution, material, age, date_acquired, price');
        $this->db->from('items');*/
        
        $keywords = $this->input->post('keywords');
        $where = "(`Item_name`  LIKE '%".$keywords."%' OR  `attribution`  LIKE '%".$keywords."%' OR  `material`  LIKE '%".$keywords."%' OR  `age`  LIKE '%".$keywords."%' OR  `description`  LIKE '%".$keywords."%')";
        $this->db->where($where, NULL, FALSE);
        $query = $this->db->get_where('items', $where);
        return $query->result();
        
        
    }

The issue is that they search returns all entries in the database instead of only those that are relevant.

Thanks,
Watson

[eluser]slowgary[/eluser]
You should turn on profiling and look at the actual query that is being generated...

Code:

$this->output->enable_profiler(TRUE);

You shouldn't need to call $this->db->where() AND $this->db->get_where(). That seems redundant. Additionally, I think your use of the first where() call is incorrect.

[eluser]Brokenegg.wb[/eluser]
you are a lifesaver. so i cleaned up the code and got everything working, the issue ended up being a spelling discrepancy (you'd think I'd know better by now) so I've got it working with this code:

The Controller:

Code:

function search()
    {
        $this->load->library('pagination');
        $this->load->library('table');
        
        $data['records'] = $this->site_db_upload->get_records_store_search();
        
        
        $this->load->view(', $data);
    }

site_db_upload:

Code:

function get_records_store_search()
    {
    
         $keywords = $this->input->post('keyword');
        $where = "(`item_name`  LIKE '%$keywords%' OR  `attribution`  LIKE '%$keywords%' OR  `material`  LIKE '%$keywords%' OR  `age`  LIKE '%$keywords%' OR  `description`  LIKE '%$keywords%')";
        $query = $this->db->get_where('items', $where);
        $config['base_url'] = 'http://www.rareintaglio.com/index.php/store';
        $config['total_rows'] =  $this->db->get_where('items', $where);
        $config['per_page'] = 9;
        $config['num_links'] = 7;
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';
        return $query->result();
    }

I still have to run a test for a search that returns multiple pages to be certain that everything is hunky dory, but i think the issue is solved. Thanks!

[eluser]slowgary[/eluser]
One other suggestion... if you had an item named "Rubber Baby Buggy Bumpers" and someone searched for "rubber bumpers", your current method would not return any results. I know you've got a lot of fields to search on, but you may want to consider exploding keywords by a blank space and looking for each word individually. This would really depend on how you think people will use your search.

Code:

$where = array();
$temp = array();
$keywords = "rubber bumper";
$keywords = explode(' ', $keywords);

$tables = array(
     'item_name',
     'attribution',
     'material',
     'age',
     'description'
);

foreach($tables as $table)
{
     foreach($keywords as $keyword)
     {
          $temp[] = "`$table` LIKE '%$keyword%'";
     }

     $where[] = '('.implode($temp, ' AND ').')';
     $temp = array();
}

$where = implode($where, "\nOR ");

That code produces the following:

Code:

(`item_name` LIKE '%rubber%' AND `item_name` LIKE '%bumper%')
OR (`attribution` LIKE '%rubber%' AND `attribution` LIKE '%bumper%')
OR (`material` LIKE '%rubber%' AND `material` LIKE '%bumper%')
OR (`age` LIKE '%rubber%' AND `age` LIKE '%bumper%')
OR (`description` LIKE '%rubber%' AND `description` LIKE '%bumper%')