bhbilbao Posted January 12, 2010 Share Posted January 12, 2010 When filtering: 1054 - Unknown column 'p2c.categories_id' in 'where clause' select count(*) as total from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '3' and s.status = '1' and p2c.categories_id=11 [TEP STOP] My categories: categories_id categories_image parent_id sort_order date_added last_modified901 0 1 08/12/2009 0:43 08/12/2009 0:50 910 0 10 08/12/2009 0:43 04/01/2010 11:23 911 0 11 08/12/2009 0:43 05/01/2010 11:50 912 0 12 08/12/2009 0:43 08/12/2009 0:50 913 0 13 08/12/2009 0:43 08/12/2009 0:50 914 0 14 08/12/2009 0:43 08/12/2009 0:50 915 0 15 08/12/2009 0:43 08/12/2009 0:50 916 0 16 08/12/2009 0:43 08/12/2009 0:50 917 0 17 08/12/2009 0:43 08/12/2009 0:50 918 0 18 08/12/2009 0:43 08/12/2009 0:50 919 0 19 08/12/2009 0:43 08/12/2009 0:50 902 0 2 08/12/2009 0:43 08/12/2009 0:50 920 0 20 08/12/2009 0:43 08/12/2009 0:50 921 0 21 08/12/2009 0:43 08/12/2009 0:50 922 0 22 08/12/2009 0:43 08/12/2009 0:50 923 0 23 08/12/2009 0:43 08/12/2009 0:50 924 0 24 08/12/2009 0:43 04/01/2010 10:17 925 0 25 08/12/2009 0:43 08/12/2009 0:50 926 0 26 08/12/2009 0:43 08/12/2009 0:50 927 0 27 08/12/2009 0:43 08/12/2009 0:50 903 0 3 08/12/2009 0:43 08/12/2009 0:50 904 0 4 08/12/2009 0:43 08/12/2009 0:50 905 0 5 08/12/2009 0:43 04/01/2010 11:23 906 0 6 08/12/2009 0:43 08/12/2009 0:50 907 0 7 08/12/2009 0:43 08/12/2009 0:50 908 0 8 08/12/2009 0:43 08/12/2009 0:50 909 0 9 08/12/2009 0:43 08/12/2009 0:50 4 901 4 08/12/2009 0:43 13/12/2009 20:27 99 909 99 08/12/2009 0:43 08/12/2009 0:50 Quote Link to comment Share on other sites More sharing options...
Guest Posted January 13, 2010 Share Posted January 13, 2010 I installed this today and it does not work for me, either. The dropdown menu appears fine on specials.php and is populated with all of my categories. But, when I select a category to filter the specials list with, I get this: 1109 - Unknown table 'p2c' in where clause select count(*) as total from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' and p2c.categories_id=33 I use osc2.2-MS2...PHP 5.2.9 and MySQL 4.1.22. The contrib offers the following query in catalog/specials.php to accomplish this filtering: $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'"; if($categories_id > 0) $specials_query_raw .= " and p2c.categories_id=".(int)$categories_id; $specials_query_raw .= " order by s.specials_date_added DESC"; Can any of you troubleshoot what may be wrong with the query? I'm thinking that p2c isn't 'defined' in the query. This would be fabulous if it worked. - Andrea Quote Link to comment Share on other sites More sharing options...
FOD Posted January 14, 2010 Share Posted January 14, 2010 Try this, probably a long winded way of doing it but it works for me. FIND $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'"; if($categories_id > 0) $specials_query_raw .= " and p2c.categories_id=".(int)$categories_id; $specials_query_raw .= " order by s.specials_date_added DESC"; REPLACE WITH if($categories_id > 0) { $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and p.products_id = p2c.products_id and p2c.categories_id=".(int)$categories_id." order by s.specials_date_added DESC"; } else { $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";; } Quote You can kill the King, but you can't kill the King Riddim... Link to comment Share on other sites More sharing options...
Guest Posted January 14, 2010 Share Posted January 14, 2010 Thank you FOD. That worked for me too. :) Is it possible to filter by categories. ex. I have category called Car. This Car category has alot of sub-categories like Brakes, Tires and windshields and so on... Is it possible if you choose Car in the drop down menu, to list all the specials in Brakes, Tires and Windshields at once ? Quote Link to comment Share on other sites More sharing options...
bhbilbao Posted January 14, 2010 Author Share Posted January 14, 2010 Thanks FOD. Also worked for me. A point for you. Quote Link to comment Share on other sites More sharing options...
Guest Posted January 15, 2010 Share Posted January 15, 2010 Thanks from me, too. :thumbsup: That works! I have the same concern as 87up. Is it possible to have that query filter specials when selecting the main category (even though there are no products actually in it) rather than have to select the sub-categories below them to find the specials? Either way, this is terrific help you gave, FOD, Thank You! - Andrea Quote Link to comment Share on other sites More sharing options...
Guest Posted January 24, 2010 Share Posted January 24, 2010 Monkeying with the query and I'm getting somewhere. At present, the specials will only show when you click on the category they're actually listed in. For example, if you have the following category setup: People Magazine The 90's 1991 1992 1993 The item on special in year 1993 will only appear onscreen when you select 1993 from the dropdown. If you select The 90's you get zero results. Same with the main parent category of People Magazine. I've changed the query, and this enables you to see the specials for 1993 when selecting either 1993 or The 90's. Replace what FOD offered with this: if (isset($categories_id)){ if (empty($categories_id) && $categories_id==""){ $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC"; } else{ $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " pcat, " . TABLE_CATEGORIES . " cat where p.products_status = '1' and p.products_id = pcat.products_id and pcat.categories_id = cat.categories_id and ((cat.categories_id = " . $categories_id . " or cat.parent_id = " . $categories_id . ")) and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC"; } } Still can't figure out how to select the main category of People Magazine and have it display specials for all the cats beneath it, but...it's better than nothing! Perhaps some query-gurus out there can take a shot at it? - Andrea Quote Link to comment Share on other sites More sharing options...
Guest Posted September 15, 2010 Share Posted September 15, 2010 Hi Great contrib, but is it possible only to list top categories in the list? Eg. I have Car Brakes Wheels Windows I only want to show "Car" and not "Brakes, Wheels, Windows" etc. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.