Require Search Field to extract more data from a different table

[callingrohit]

hi all,

I have been trying to figure this out but have lost on this one for sure....

basically the search field works fine when i search a subcategory which has some products in it but if i search the parent category's name, the search comes out with NO PRODUCTS.

What can I do to modify the sql query to get the search to list out all subcategories and their products when their parent category is searched for.

example -

Parent category - A
Sub-category - BB >> Products
Sub-category - CC >> Products
Sub-category - DD
Sub-category - EE >> Products

In the above scenario, in my search, as of now, if I search using BB/CC/EE, all the products from their respective categories get listed.

But if I search A/DD, since they do not have any products, the search returns no products.

Ideally what I would like to accomplish is if A is searched, all products from BB-CC-EE should be returned.

If DD is searched for, all products from EE should be listed.

The reason I wanna accomplish this especially for products to be returned when A is the search input is because in my scenario A would be the BRAND NAME. I cannot put this under manufacturer table, please don't ask me why because thats a long a different story.

I'm just not able to figure this out because i'm stuck with the idea that it cannot be done since the product to category table will not have the entry for the category_id of A/DD. So obviously no products are displayed....

if someone can just discuss this problem and guide me..i would be highly grateful.

Thank you.

callingrohit

[yellow1912]

Because of the way products are associated with categories, you will have to re-code the search function to achieve what you want.

[callingrohit]

hi,

thank you for the prompt reply.

can you please guide me.... i do know that i would need to recode but i've tried modifying the sql query that is sent but had no success.

$where_str = " WHERE (p.products_status = 1
               AND p.products_id = pd.products_id
               AND pd.language_id = :languagesID
               AND p.products_id = p2c.products_id
           AND cd.categories_id = c.categories_id 
               AND p2c.categories_id = c.categories_id "; 

because there is a statement - " AND p2c.categories_id = c.categories_id " which is the main one that would not allow A/DD to return results. I have added the statement "AND cd.categories_id = c.categories_id" so that when BB/CC/EE are searched their respective products are displayed.

To help the latter i also added the category description table in the query since it contains the name of the categories

$from_str = "FROM (" . TABLE_PRODUCTS . " p
             LEFT JOIN " . TABLE_MANUFACTURERS . " m
             USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . 
             TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
             LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd
             ON mtpd.products_id= p2c.products_id
             AND mtpd.language_id = :languagesID"; 

and then added " OR cd.categories_name LIKE '%:keywords%' " to allow for BB/CC/EE to return results.

$where_str .= "(pd.products_name LIKE '%:keywords%'
                                         OR p.products_model
                                         LIKE '%:keywords%'
                                         OR m.manufacturers_name
                                         LIKE '%:keywords%'
                    OR cd.categories_name 
                                         LIKE '%:keywords%'"; 

thanks

[callingrohit]

just a quick note....i have figured out a way to get the products within BB/CC/EE to be listed when A is searched.

This is by manually going into the products within BB/CC/EE and entering the word A into the metatags keywords. All products which will have the metatag keyword as A will then get listed when A is searched. Similarly for DD to return products listed within its sub-category EE, the metatags keywords for all products within EE should have DD also as a keyword.

This method will allow search to return products when either A/DD is searched for. Ofcourse A will be a broad search and will return all products within its subcategories whereas when DD is searched only DD's subcategory EE's products are listed....

since this is a workaround can someone please be kind enough and suggest a way to automatically insert the parent category name as a metatag keyword to accomplish what i want.

So products in BB/CC will automatically have A as their metatag keyword and EE products will have A (super-parent) & DD(parent) as its metatag keyword.

thanks

[callingrohit]

This post can be closed because I was able to solve my issue. In a way the forum helped because as I was writing the points I realised metatags keywords will be of great help here.

So anyways using a custom built PRODUCT BULK UPLOAD script I added the functionality to accept metatag keywords from the csv file which will be uploaded and those metatag keywords are inserted into the Table: meta_tags_products_description of the DB.

This way both A & DD when searched return results and not return empty.