Search query using multiple tables

Search query using multiple tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Search query using multiple tables (/showthread.php?tid=72735)

Search query using multiple tables - palmer - 02-03-2019

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! Smile

RE: Search query using multiple tables - php_rocs - 02-05-2019

@palmer,

Are you familiar with database views? You could create a view that has all of the mentioned data that is searchable?

Or

You could create a complex query that has all of the searchable content by using a specific SQL join.