+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Need Help With Active Record For Search Function (/showthread.php?tid=26904)
Need Help With Active Record For Search Function - El Forum - 01-26-2010
[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?
Need Help With Active Record For Search Function - El Forum - 01-27-2010
[eluser]Jay Logan[/eluser]
Anyone?
Need Help With Active Record For Search Function - El Forum - 01-27-2010
[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.
Need Help With Active Record For Search Function - El Forum - 01-27-2010
[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.
Need Help With Active Record For Search Function - El Forum - 01-28-2010
[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` descLooks 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`)?
Need Help With Active Record For Search Function - El Forum - 01-28-2010
[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` descNeed Help With Active Record For Search Function - El Forum - 01-28-2010
[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);Need Help With Active Record For Search Function - El Forum - 01-28-2010
[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;
}Need Help With Active Record For Search Function - El Forum - 01-28-2010
[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-guide/database/queries.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) );