CodeIgniter Forums

Full Version: Need Help With Active Record For Search Function
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Jay Logan[/eluser]
I have a database full of customers that get assigned to different locations around country. One of my location managers will log in and see a list of his customers, no problem. But I want to give them the ability to search there their filtered list of customers and I just can't seem to get the where_in & like function to work together. Here is what I have so far.

Code:
function search($locations = FALSE)
    {
        $this->db->select('JL_customers.*, JL_customers_status.name as status_name, JL_customers_status.color as status_color');
        $this->db->from('JL_customers');
        
        if ($locations) {

            foreach ($locations as $location) {

                $this->db->or_where('assn_location_id', $location);

            }

        }
                
        $this->db->like('name_first', $this->input->post('keywords'));
        //$this->db->or_like('name_last', $this->input->post('keywords'));
        //$this->db->or_like('email_home', $this->input->post('keywords'));

        
        $this->db->order_by('date_last_modified', 'desc');
        $this->db->join('JL_customers_status', 'JL_customers_status.id = JL_customers.status', 'left');
        $get_results = $this->db->get();
        $results = $get_results->result_array();
        return $results;
    }


This will let me find customers of a location if i enter the first name. But when I uncomment the or_like function it starts searching the entire database. Any help?

El Forum

[eluser]Jay Logan[/eluser]
Anyone?

El Forum

[eluser]danmontgomery[/eluser]
I don't see any calls to where_in...?

Enable profiler and paste the SQL that's being run, along with what you want it to be running instead.

El Forum

[eluser]Joshua Logsdon[/eluser]
Yeah, like noctrum said, seeing the SQL being built may be easier to pinpoint what's going on...

You may be unexpectedly finding "OR name_last LIKE ''" or something that is making the search pull the entire database as you're saying. Also it might be a little "OR" crazy and somehow your filters aren't working as expected... something that doesn't pop out in the code but would in the SQL.

El Forum

[eluser]Jay Logan[/eluser]
Here is the query from the profiler.

Code:
SELECT `JL_customers`.*, `JL_customers_status`.`name` as status_name, `JL_customers_status`.`color` as status_color
FROM (`JL_customers`)
LEFT JOIN `JL_customers_status` ON `JL_customers_status`.`id` = `JL_customers`.`status`
WHERE `assn_location_id` = '203'
AND  `name_first`  LIKE '%test%'
OR  `name_last`  LIKE '%test%'
OR  `email_home`  LIKE '%test%'
ORDER BY `date_last_modified` desc

Looks like after it searches the name_first column for test, it ignores the WHERE `assn_location_id`. Is there a way to keep the WHERE `assn_location_id` running while it checks other columns (`name_last` and `email_home`)?

El Forum

[eluser]davidbehler[/eluser]
You need to use parenthesis, it should be
Code:
SELECT `JL_customers`.*, `JL_customers_status`.`name` as status_name, `JL_customers_status`.`color` as status_color
FROM (`JL_customers`)
LEFT JOIN `JL_customers_status` ON `JL_customers_status`.`id` = `JL_customers`.`status`
WHERE `assn_location_id` = '203' AND  
(`name_first`  LIKE '%test%'
OR  `name_last`  LIKE '%test%'
OR  `email_home`  LIKE '%test%')
ORDER BY `date_last_modified` desc
The problem is, this cannot be easily achieved active record. You will propably have to use plain old sql for that.

El Forum

[eluser]Joshua Logsdon[/eluser]
waldmeister beat me to it. Taken from the user guide you can still use the Active Record methods after you build your statement string to be in parenthesis as needed:

Code:
$where = "(`name_first`  LIKE '%test%' OR  `name_last`  LIKE '%test%' OR  `email_home`  LIKE '%test%')";
$this->db->where($where, NULL, FALSE);

El Forum

[eluser]Jay Logan[/eluser]
Ahhhhhhhh. Thanks so much. This is really going to help me with a lot of new features I'm working on. Here is the final function.

Code:
function search($locations = FALSE)
    {
        $this->db->select('JL_customers.*, JL_customers_status.name as status_name, JL_customers_status.color as status_color');
        $this->db->from('JL_customers');
        
        if ($locations) {

            foreach ($locations as $location) {

                $this->db->or_where('assn_location_id', $location);

            }

        }
        
        $keywords = $this->input->post('keywords');
        $where = "(`name_first`  LIKE '".$keywords."' OR  `name_last`  LIKE '".$keywords."' OR  `email_home`  LIKE '".$keywords."')";
        $this->db->where($where, NULL, FALSE);
        $this->db->order_by('date_last_modified', 'desc');
        $this->db->join('JL_customers_status', 'JL_customers_status.id = JL_customers.status', 'left');
        $get_results = $this->db->get();
        $results = $get_results->result_array();
        return $results;
        
    }

El Forum

[eluser]Joshua Logsdon[/eluser]
Looks good and good luck with it!

As a parting note, I would feel negligent if I didn't add this in on Escaping Queries:
http://ellislab.com/codeigniter/user-gui...eries.html

So you may want to use this when building your new WHERE clause where applicable:
Code:
..... LIKE '". $this->db->escape_like_str($keywords) ."' .....

And if you want to be extra safe and do any XSS filtering:
Code:
$keywords = $this->input->post('keywords', TRUE);

And if you are only going to use $keywords in the LIKE area you could just do it all at once:
Code:
$keywords = $this->db->escape_like_str( $this->input->post('keywords', TRUE) );
and keep $keywords plugged into the $where like you have now.