When Searching for a Second Product

I am building a search feature on a website that lists companies and their products.

A user can search for companies by the type of product they offer and the role they play in the distribution network. 

They can search for one or two product types. If searching for two product types, the results should show only the companies who provide both product types.

It all works okay except when the second product type is added, then it finds zero results.

My tables look like something this …

TABLE: COMPANY
comp_id | comp_name | type
————————————————
1           | comp a        | manufacturer
2           | comp b        | wholesaler
3           | comp c        | manufacturer


TABLE: COMPANY_PRODUCTS
prod_id | comp_id | sys_id
——————————————————
1          | 1           |  1
2          | 1           |  2
3          | 2           |  1
4          | 2           |  2
5          | 3           |  1


TABLE: SYS_PRODUCTS
sys_id | serv_name
——————————————————
1        | pens
2        | pads

When a user searches for MANUFACTURING companies who supply PENS and PADS, the only result should be COMP A.

A summary of the code I’m using is …

Code:

// set company  type
$type = $this->input->post(‘type’);

// set product searches
$product_1 = $this->input->post(‘product_1’);
$product_2 = $this->input->post(‘product_2’);

// run query
$this->db->select('*');
$this->db->from('company');
$this->db->where(‘company.type’, $type);

$this->db->join('company_products', 'company_products.comp_id = company.comp_id', 'left');

$this->db->where('company_products.sys_id', $product_1);
$this->db->where('company_products.sys_id', $product_2);

If the user searches for one product, it works fine. It only breaks when the user searches for two products.

Can anybody help?

Your current query will produce

WHERE 'company_products.sys_id' = $product_1
AND 'company_products.sys_id' = $product_2

The second where should be an or_where so it will produce

WHERE 'company_products.sys_id' = $product_1
OR 'company_products.sys_id' = $product_2

PHP Code:

$this->db->where('company_products.sys_id', $product_1);
$this->db->or_where('company_products.sys_id', $product_2); 


Thanks Martin7483, but your solution would show companies with product_1 - OR -  companies with product_2.

I need it to show companies with product_1 - AND - companies with product_2.

I think I have found the solution myself ...

Code:

$this->db->join('company_products as cp1', 'cp1.comp_id = company.comp_id', 'left');
$this->db->where('cp1.sys_id', $product_1);

$this->db->join('company_products as cp2', 'cp2.comp_id = company.comp_id', 'left');
$this->db->where('cp2.sys_id', $product_2);

That seems to work ok but I haven't exhausted all tests yet.

Yep, that is indeed beter