CodeIgniter Forums

Full Version: Searching multiple tables - fields
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.
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?
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
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();
  }

(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.
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.