Hi, have looked online for a while to find a solution for this but can't find anything specifically related to my question so posting here.
I'm building an e-commerce site that includes a search bar.
The products in my database are set up as follows:
products
Code:
id product_title product_cat product_sub_cat
16 'Robin Red Breast' 3 6
product_cats
Code:
id cat_name
3 animals
4 plants
product_sub_cats
Code:
id sub_cat_name parent_cat_id
6 birds 3
7 fish 3
8 trees 4
I'm trying to find products that are related to a search query. At the moment, the search query looks in the products table for any products that contain anything similar to the query in their title field. E.g:
Code:
$this->db->from('products');
$this->db->like('product_title', $query);
$results = $this->db->get()->result();
What I want to do, is also use the query to search the category and sub-category tables and find any categories which have a similar name to the search query, then fetch any products that are in this category. For example, at the moment, the search query 'Robin Red' will find the 'Robin Red Breast' product, but searching for 'birds' does not return anything.
What would be the best way of going about making the latter possible using active record?
Does this mean searching the cats and sub_cats tables separately, fetching the id's of any rows that match the query, then selecting any rows from the products table that match the id('s)?
Cheers!