产品搜索3个数据库表

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?

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%'

Why don't you use lucene-solr for this?

You need to do a join, on all three tables, with a where clause that queries the fields you want to search. Something like this:

select * from 
products
inner join categories on products.catID = categories.categoryID
inner join sub_categories on products.subCatID = sub_categories.categoryID
where
products.shortDescription like '%query%'
or products.longDescription like '%query%'
or categories.categoryName like '%query%'
or sub_categories.categoryName like '%query%'
;

where the query is the search query string.

this can be helpful to you.....

SELECT * FROM products AS  PT 
LEFT JOIN Categories AS CT ON CT.catID = PT.catID
LEFT JOIN sub_categories  AS SCT ON SCT.subCatID = PT.subCatID
WHERE 
  PT.active = 'YES' AND 
  (PT.shortDescription LIKE '%shortDescription%' OR 
   PT.longDescription LIKE '%longDescription%' OR 
   CT.category LIKE '%category%'  OR 
   SCT.categoryID LIKE '%sub category%' )

you just put your values using php varrible you can get your search results.