Need Help With Active Record For Search Function |
[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 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?
[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.
[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.
[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 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`)?
[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
[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%')";
[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)
[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) ); |
Welcome Guest, Not a member yet? Register Sign In |