产品搜索3个数据库表

Question

I am trying to create a product search feature on an E-commerce website I am building but am having a little trouble.

I have 3 tables (categories, sub_categories, products)

Categories table fields: (categoryID, categoryName, active, image)

sub_categories table fields: (categoryID, categoryName, parentCatID, active, image)

products table fields: (productID, shortDescription, longDescription, image, catID, subCatID, active, price, delivery, weight)

Im trying to get my search to find a product if a user types in any part of the short description or long description or if the user types in any part of the category or sub category names it should find all products within those categories.

I dont know whether to do a JOIN or multiple SQL queries. to be honest i've been tinkering with it for a few hours but havnt really gotten anywhere and am now back at the drawing board asking for help

my first attempt looked like this:

 $catSelect = mysqli_query($con,"SELECT * FROM categories WHERE categoryName LIKE '%{$term}%'"); 
            $row1 = mysqli_fetch_row($catSelect);
 $subCatSelect = mysqli_query($con,"SELECT * FROM sub_categories WHERE categoryName LIKE '%{$term}%' OR parentCatID = '%{$row1[0]}%'");
            $row2 = mysqli_fetch_row($subcatSelect);
            $productSelect = mysqli_query($con,"SELECT * FROM products WHERE short_description LIKE '%{$term}%' OR long_description LIKE '%{$term}%' OR subCatID = '%{$row2[0]}%' OR catID = '%{$row1[0]}%'"); 

my final attempt looks like this

mysqli_query($con,"SELECT * FROM products  INNER JOIN categories ON products.catID = categories.categoryID WHERE categories.categoryName LIKE '%{$term}%'") or die(mysqli_error());

Could someone help me with the SQL query I need to use?

Answer 1

Try this:

SELECT
    p.productID
FROM
    products p
LEFT JOIN categories c
    ON c.categoryID = p.catID
LEFT JOIN sub_categories sc
    ON sc.categoryID = p.subCatID
WHERE
    p.shortDescription LIKE '%keyword%'
    OR p.longDescription LIKE '%keyword%'
    OR c.categoryName LIKE '%keyword%'
    OR sc.categoryName LIKE '%keyword%'