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 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);
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'); That seems to work ok but I haven't exhausted all tests yet.
|
Welcome Guest, Not a member yet? Register Sign In |