alman Posted February 25, 2009 Share Posted February 25, 2009 (edited) Hey guys have to say great contribution I have a small problem someone might be able to help me with I get a error when I do a price filter 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' select count(distinct p.products_id) as total from ((products p) left join manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '209') where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '1' and p2c.categories_id = '57' and m.manufacturers_id = '9' and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 90) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 150) this error is for product model search 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 12, 12' at line 1 select pd.products_name, p.products_image, 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(( products_description pd, products p )left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c )left join specials s on p.products_id = s.products_id left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '57' order by limit 12, 12 manufacturer filter, price , product name work but manufacturers and model do not I have enable/disable contribution installed I'm not sure it would be affecting it I followed the instruction about index.php to configure also Thanks alman ps it is the literally the last php quote <?php //} ?> Edited February 25, 2009 by alman Quote Link to comment Share on other sites More sharing options...
scottybwoy Posted March 10, 2009 Author Share Posted March 10, 2009 Then the delete the very last line.. Maybe this is what I am missing. Exactly delete what last line are we talking? The filterlist shows up when someone comes from a direct link to a manufaturers list page. But try to enter a keyword or display by more pages it redirects to homepage. I noticed the basename file when this happens is index.php - not the category url as normal if that makes any sense. Anyway is there a way to add a category sort when on a sitewide manufacturers list page? This contrib is called by the products listing module, installed as standard. So when a page calls the products listing module, this contrib will appear. Unfortunately different pages use different methods to call/sort products. When one of the filter parameters is used it then sends the data to whatever file called the products listing module, so products listing knows how to sort it. I did try changing this to a file that definately knows how to sort it, i.e advanced search, but then customers would only be able to filter through what they have already filtered. i.e. say they chose manufacturer a, they would not be able to see the results for manufacturer b in the same category, in fact if they chose that, they would see no products at all. Also I don't see where the manufacturers list page is on my site, but I assume that is called by index.php which doesn't understand the search parameters, so would just show the home page. If you tell me your site, I can go have a quick look to see where you mean so I can be a bit more helpful. Then the delete the very last line.. Maybe this is what I am missing. Exactly delete what last line are we talking? Last line of filterlist.php <?php } ?> Yeah that could f**k up the rest of your script, if you remove one line without the other. 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' select count(distinct p.products_id) as total from ((products p) left join manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '209') where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '1' and p2c.categories_id = '57' and m.manufacturers_id = '9' and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 90) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 150) this error is for product model search 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 12, 12' at line 1 select pd.products_name, p.products_image, 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(( products_description pd, products p )left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c )left join specials s on p.products_id = s.products_id left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '57' order by limit 12, 12 manufacturer filter, price , product name work but manufacturers and model do not I have enable/disable contribution installed I'm not sure it would be affecting it I followed the instruction about index.php to configure also Firstly check your db to see if you actually have products_tax_class_id in your products table. It's a bit too much for me to look at at the moment, but it looks like you may need to check any amendments you made to the index.php where I specified in an earlier post. Please note the code I posted cannot be copied and pasted directly to yours as it will be different for each individual site, so I hope you backed yours up. If all else fails just use the original index.php and play around with the filterlist.php Lines 418 - 421 untill you get the right order. Good Luck. P.S that enable/disable contrib shouldn't affect this one. Quote Link to comment Share on other sites More sharing options...
gardoscatalin Posted April 22, 2009 Share Posted April 22, 2009 Hello, Can someone help me with a problem ? After I use the filter, in results I want to have a column with the product description. Not for sorting just to show'it. Thanks and sorry for my very bad english. Catalin Quote Link to comment Share on other sites More sharing options...
godino1 Posted June 16, 2009 Share Posted June 16, 2009 Hi there, Can anyone help me out? Installed this add on great contribution every shows up fine but Sort items by : Model | Product Name | Manufacturer | Price [+] part is not clickable it's just words not links. Can anyone tell me whats wrong? Quote Link to comment Share on other sites More sharing options...
godino1 Posted June 17, 2009 Share Posted June 17, 2009 Hi there, Can anyone help me out? Installed this add on great contribution every shows up fine but Sort items by : Model | Product Name | Manufacturer | Price [+] part is not clickable it's just words not links. Can anyone tell me whats wrong? Ayone? Quote Link to comment Share on other sites More sharing options...
godino1 Posted June 18, 2009 Share Posted June 18, 2009 <div> <?php // Sort columns by ??? You may need to rearrange the numbers in order of your columns in product listing echo SORT_BY . tep_create_sort_heading($HTTP_GET_VARS['sort'], 2, TABLE_HEADING_MODEL) . ' | ' . tep_create_sort_heading($HTTP_GET_VARS['sort'], 1, TABLE_HEADING_PRODUCTS) . ' | ' . tep_create_sort_heading($HTTP_GET_VARS['sort'], 2, TABLE_HEADING_MANUFACTURER) . ' | ' . tep_create_sort_heading($HTTP_GET_VARS['sort'], 5, TABLE_HEADING_PRICE); ?> </div> This is the bit that ain't working it just appears as text not clickable links Quote Link to comment Share on other sites More sharing options...
maxtors Posted October 24, 2009 Share Posted October 24, 2009 Hello mcafee secure gave me a problem with this contribution with an SQL Injection error does anyone know how i can fix this error with out removing the contribution thanks Quote Link to comment Share on other sites More sharing options...
Jan Zonjee Posted October 24, 2009 Share Posted October 24, 2009 Hello mcafee secure gave me a problem with this contribution with an SQL Injection error does anyone know how i can fix this error with out removing the contribution thanks Perhaps it has to do with the sorting (filterlist.php line 332 and further). In older osC versions and this file the "ordering" code on index.php starts with: $sort_col = substr($HTTP_POST_VARS['sort'], 0 , 1); $sort_order = substr($HTTP_POST_VARS['sort'], 1); $order_str = ' order by '; switch ($column_list[$sort_col-1]) { case 'PRODUCT_LIST_MODEL': $order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name"; break; In later osC versions the $order_str = ' order by '; was removed and added further on (for each case). Like in the above piece of code: $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1); $sort_order = substr($HTTP_GET_VARS['sort'], 1); switch ($column_list[$sort_col-1]) { case 'PRODUCT_LIST_MODEL': $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; Quote Link to comment Share on other sites More sharing options...
maxtors Posted October 26, 2009 Share Posted October 26, 2009 That did not fix it anything else i can try? Perhaps it has to do with the sorting (filterlist.php line 332 and further). In older osC versions and this file the "ordering" code on index.php starts with: $sort_col = substr($HTTP_POST_VARS['sort'], 0 , 1); $sort_order = substr($HTTP_POST_VARS['sort'], 1); $order_str = ' order by '; switch ($column_list[$sort_col-1]) { case 'PRODUCT_LIST_MODEL': $order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name"; break; In later osC versions the $order_str = ' order by '; was removed and added further on (for each case). Like in the above piece of code: $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1); $sort_order = substr($HTTP_GET_VARS['sort'], 1); switch ($column_list[$sort_col-1]) { case 'PRODUCT_LIST_MODEL': $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; Quote Link to comment Share on other sites More sharing options...
jonathanbastin Posted October 30, 2009 Share Posted October 30, 2009 Hi I am running SEO URL's with a few other mods, but I have a problem with this one were the show manufacturers and X number of item returns nothing. If I use the default show manufacturers it works fine. Is this a known fault. Quote Link to comment Share on other sites More sharing options...
Guest Posted December 5, 2009 Share Posted December 5, 2009 How would this need to be modified to find only products that are on special? Has anyone done this and can help? Quote Link to comment Share on other sites More sharing options...
Guest Posted December 5, 2009 Share Posted December 5, 2009 One other question: when you search a keyword that doesn't exist, the filter disappears and you have to choose 'back' in your browser to enter a different choice. What do we have to modify to keep it there after "no results" are returned? Quote Link to comment Share on other sites More sharing options...
ronrrico Posted December 9, 2009 Share Posted December 9, 2009 Hi Guys, anybody has a demo on this? Thanks! Quote Link to comment Share on other sites More sharing options...
Guest Posted December 17, 2009 Share Posted December 17, 2009 I'm getting the 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' error since upgrading to Mysql 5. Works fine on SQL4 with no changes. Have read through and tried all suggestions in these posts to no avail. Has anyone got this to work on SQL5? Am using RC2a and have no problems anywhere else in the store - just when running a shop by price query using this contribution. Don't want to take it out as it would be a great contribution if I can get it to work on sql5 but don't know enough about sql to fix the queries..... Please help! Quote Link to comment Share on other sites More sharing options...
j-variety Posted July 17, 2010 Share Posted July 17, 2010 Hello. I am having trouble to get this great contribution working on my site. I followed the entire instruction, and upon testing I received the following errors: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by pd.products_name desc limit 0, 20' at line 1 select pd.products_name, p.products_image, p.products_id, p.products_image_med, p.short_desc, 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 (((products_description pd, products p) left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c) left join specials s on p.products_id = s.products_id) left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '199' order by order by pd.products_name desc limit 0, 20 [TEP STOP] Please help. My OSC version is RC2a. 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.