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?
