Ahmed Aws Ahmed Aws - 21 days ago 5
MySQL Question

search in category and sub category

I have two tables: one contains products and the second contains the category. I want to be able to search by category such that when I search in the parent category, it searches in both the parent and subcategory.

My goal:
Perform a search in "cars" >> will search in chevrolet and toyota

How can I achieve this result?



mysqli_query($conn,"select * from cat where parent=''");





mysqli pic

Answer

Since you didn't show your table structure, I'm gong to make some guesses. I also hope this answer isn't too simplistic, but without more to go on it seems like you're looking for the fundamental idea.

Assuming in your example "cars" is in the category table, and "chevrolet" and "toyota" are in the products table, Then you need another column in the products table saying what category each product is.

so if you have a category table like:

cat_id | cat_name
-----------------
  1    | cars
  2    | appliances

and you have a products table like:

product_id | product_name | cat_id
----------------------------------
  1        | chevrolet    |  1
  2        | kitchenaid   |  2
  3        | toyota       |  1

The important thing is that each item in the products table has a reference to what the category is. This is what relational databases do: they provide a way to show the relationship between different entities.

Then to get a list of all the products in the category "cars", you just need to select the ones in the proper category:

SELECT product_name from products WHERE cat_id = 1;

If you also want to include information from the category table in your result, you can join the tables:

SELECT product_name, cat_name from products
  JOIN categories ON products.cat_id = categories.cat_id
  WHERE categories.cat_id = 1

This will get you the result:

product_name | cat_name
------------------------
  chevrolet  | cars
  toyota     | cars

In some cases it makes sense to put the categories and the products in the same table (if you want to have a tree of values of arbitrary depth, for instance, where 'chevrolet' is parent to 'volt', and 'impala', etc.).

The same query still works, but now you join the table with itself (I'm using column names based on your comment below):

SELECT child.name, parent.name 
  FROM products AS parent
  JOIN products AS child ON parent.id = child.parent
  WHERE parent.id = 1

This will get you pretty much the same result as above. Although it's the same table joining to itself, I use AS to differentiate between the two references.

Comments