markmca Posted July 2, 2004 Posted July 2, 2004 I would like to retrieve all the products which fall under the main category that is We have Accesories->Car accesories->General Accesories->Car accesories->Other this is the flow of the categories I would like to view all the products which are under category Accesories which should include all the products of all the subcategories under Accesories the tables all the same used in the oscommerce Pls anyone help me out
♥bruyndoncx Posted July 2, 2004 Posted July 2, 2004 To write such a sql query it is only possible to retrieve all products if you ALWAYS have the same number of subcategories; i.e. in this example you have main category accessories, then Sub 1 Car Accessories, Then Sub 2 General and Other. If you write a query to have all products listed; logically, you have to fix where all the products will be located; in this case under Sub 2. Would you have any that were located under Sub 1, or if you would have another branch that has Sub 3 category, it would not find these with the same query; but seperate queries would need to be defined. Hope this clarifies a bit how it works; now the question is, do you only have products listed under Sub 2 category level ? KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
markmca Posted July 2, 2004 Author Posted July 2, 2004 I would like to retrieve all the products which fall under the main category that is We have Accesories->Car accesories->General Accesories->Car accesories->Other this is the flow of the categories I would like to view all the products which are under category Accesories which should include all the products of all the subcategories under Accesories the tables all the same used in the oscommerce Pls anyone help me out
markmca Posted July 2, 2004 Author Posted July 2, 2004 Well thank you for the response Let me make clear Like I have Accesories(category) ->Car Acceesories(subcategory) -> General(subsubcategory) -> Other(subsubcategory) This is the hierarchyof the categories and there will not be any more sub categories under subsubcategories.I would like to query all the products under which come under Accesories Thank you
♥bruyndoncx Posted July 2, 2004 Posted July 2, 2004 select mcd.categories_name, sc1d.categories_name, sc2d.categories_name, p.* , pd.* from categories_description mcd, categories mc, categories_description sc1d, categories sc1, categories_description sc2d, categories sc2,products p, products_description pd, products_to_categories ptc where mc.categories_id = mcd.categories_id and sc1.categories_id = sc1d.categories_id and sc2.categories_id = sc2d.categories_id and sc2.parent_id = sc1.categories_id and sc1.parent_id = mc.categories_id and mcd.categories_name = "Accesories" and mcd.language_id = sc1d.language_id and sc1d.language_id = sc2d.language_id and p.products_id = ptc.products_id and ptc.categories_id = sc2.categories_id and pd.language_id = sc2d.language_id; This code runs without errors; but since I don't have the categories structure like you have, haven't been able to get results back. HTH KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
guntersammet Posted July 3, 2004 Posted July 3, 2004 Try this: //get all subcategories $all_categories_array = tep_get_category_tree((int)$current_category_id, '', '0', '', true); $like_string = ''; foreach($all_categories_array AS $value){ $like_string .= $value['id'] . ', '; } $like_string = substr($like_string, 0, -2); // We show the categorie and subcategories $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$osC_Session->value('languages_id') . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id IN(" . $like_string . ")"; I hope this function is in the default catalog and the $current_category_id value is set. If not, you need copy it from admin to catalog and set the current category: tep_get_category_tree((int)$current_category_id, '', '0', '', true); HTH Gunter PS: If there is still a column in there which isn't in the default OSC and you get an error, you need to take it out.
♥bruyndoncx Posted July 3, 2004 Posted July 3, 2004 wasn't this asked here yet ? http://www.oscommerce.com/forums/index.php?sho...=0entry399011 KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
OceanRanch Posted July 3, 2004 Posted July 3, 2004 Hi Carine, You properly point out one of best reasons for not allowing double posts! Let's see how many good folks we can get to work independently solving the same problems. Oh well don't let it get you down. ;) Tom
Recommended Posts
Archived
This topic is now archived and is closed to further replies.