+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Searching multiple tables - fields (/showthread.php?tid=74617)
Searching multiple tables - fields - demyr - 10-16-2019
Hi there,
I need the logic behind multiple field search.
So far I have tried group_start() or_group_start() for nested search but did not have any proper result.
Let me explain shortly what my problem is:
I have got 2 dropdown selects and 1 input. So, there must be 2 scenarios :
The first scenario - the search without input, which is for company name :
a) results based on city only (one of the selects)
b) results based on category only (the other select)
c) results based on city and category together
The second scenario - the search with input (name of company) :
a) results based on name only
b) only city
c) only category
d) city and category together
e) city and name match
f) category and name
e) all of them chosen together
I also tried creating multiple if, elseif on controller but it didn't seem so wise. I guess, It is only OK for
if(empty($name)) { bla bla} // just to check an empty input I mean.
To sum up, Is trying group_start() correct? I need you to show me the route. Then I will try.
P.S : city, category and name are 3 different tables in my database.
RE: Searching multiple tables - fields - Wouter60 - 10-16-2019
It's important to know what results you want.
If the user searches on city, do you only need results from the cities table, or also from the category and name tables?
What's the relationship between those tables?
RE: Searching multiple tables - fields - demyr - 10-16-2019
Hello Wouter60,
thanks for your interest. Desired results are my scenarios.
The relation between them : Company is in company_table, City is in company_information_table, Category is in category_table
category_table
category_id, category_name, category_name_slug
company_table
company_id, company_name, company_bla bla, category_id
company_information_table
company_info_id, company_address, company_city, company_tel, company_email, bla bla, company_id
RE: Searching multiple tables - fields - Wouter60 - 10-16-2019
Try this:
controller:
PHP Code:
$this->load->helper('array');
$this->load->model('company_model');
$data['city'] = 'New York'; //example of city in your search box
$data['category'] = 15; //example of category_id in your search box
$s_result = $this->company_model->search_result($data);
echo '<pre>';
print_r($s_result);
echo '</pre>';
company_model:
PHP Code:
public function search_result($data)
{
$s_category = element('category',$data,NULL); //element is array helper function!
$s_city = element('city',$data,NULL);
$s_name = element('name',$data,NULL);
$this->db
->select('cot.*,cit.*,cat.*')
->from('company_table cot')
->join('company_information_table cit','cot.company_id = cit.company_id')
->join('category_table cat','cot.category_id=cat.category_id');
if ($s_category) $this->db->where('cot.category_id',$s_category);
if ($s_city) $this->db->where('cit.company_city',$s_city);
if ($s_name) $this->db->where('cot.company_name',$s_name);
$this->db->order_by('cot.company_id','ASC');
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
}
RE: Searching multiple tables - fields - demyr - 10-20-2019
(10-16-2019, 11:00 PM)Wouter60 Wrote: Try this:
controller:
PHP Code:$this->load->helper('array');
$this->load->model('company_model');
$data['city'] = 'New York'; //example of city in your search box
$data['category'] = 15; //example of category_id in your search box
$s_result = $this->company_model->search_result($data);
echo '<pre>';
print_r($s_result);
echo '</pre>';
company_model:
PHP Code:public function search_result($data)
{
$s_category = element('category',$data,NULL); //element is array helper function!
$s_city = element('city',$data,NULL);
$s_name = element('name',$data,NULL);
$this->db
->select('cot.*,cit.*,cat.*')
->from('company_table cot')
->join('company_information_table cit','cot.company_id = cit.company_id')
->join('category_table cat','cot.category_id=cat.category_id');
if ($s_category) $this->db->where('cot.category_id',$s_category);
if ($s_city) $this->db->where('cit.company_city',$s_city);
if ($s_name) $this->db->where('cot.company_name',$s_name);
$this->db->order_by('cot.company_id','ASC');
$query = $this->db->get();
if ($query->num_rows() == 0) {
return FALSE;
}
else {
return $query->result();
}
}
Thanks for your suggestions Wouter60. But I must say that I couldn't get the results I expected.
After your suggestion I tried searching on all options (selects and input) one by one in the controller using IF statements and I got results for all of them except for one of them. However, it didn't seem healthy to me. That's why I had asked the question here. Then my friend (owner of the project) changed the search criteria and now it's easy for me because now every select dropdowns must be selected and input must be filled. However, it would be good to have a tutorial anywhere on web about searching regarding multiple criterias like we see on shopping or classified websites.
RE: Searching multiple tables - fields - Wouter60 - 10-20-2019
I only focussed on the logic for searching in your database, not on the form you provide your users with. I assumed that you already managed to do that part yourself.
The 2 examples I shared:
PHP Code:
$data['city'] = 'New York'; //example of city in your search box
$data['category'] = 15; //example of category_id in your search box
were just meant to be able to test the model function, with query builder commands inside it.
In real life, $data['city'], $data['category'] and $data['name'] must get their values from your form.
There is no need for IF statements in your controller. You collect all values in $this->input->post(), put them in the $data array, and pass that to the model. The model function I wrote, detects which search variables have a value, and which haven't.