Guest Posted January 11, 2008 Share Posted January 11, 2008 i get this error: 1054 - Unknown column 'pv.products_options_values_id' in 'on clause' select pv.products_options_values_id as id, pv.products_options_values_name as text from products_options_values pv, products_options po join products_options_values_to_products_options popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =1 and po.language_id =1 group by pv.products_options_values_id, products_options_values_name [TEP STOP] i installed: Advanced Search - Attributes http://www.oscommerce.com/community/contri...l/search,search and it was working fine yester day (man that sounds soo lame) and today it went a fluke. i think it was something to do with php5 but im not sure since im not very good at php Help Any one.... Quote Link to comment Share on other sites More sharing options...
amystephenson Posted February 17, 2008 Share Posted February 17, 2008 Did you ever get this resolved. I have the same issue. Quote Link to comment Share on other sites More sharing options...
satish Posted February 17, 2008 Share Posted February 17, 2008 This is a Mysql5 related bug. Search forum for mysql5 patches apply those. Satish Quote Ask/Skype for Free osCommerce value addon/SEO suggestion tips for your site. Check My About US For who am I and what My company does. Link to comment Share on other sites More sharing options...
amystephenson Posted February 18, 2008 Share Posted February 18, 2008 Satish - Thank you for your help. I patched my pages and I still have that same problem. All I want is a drop down for attributes. My optimal idea would be to have it on the index page. Quote Link to comment Share on other sites More sharing options...
misillsam Posted March 15, 2008 Share Posted March 15, 2008 Me too same error, never resolved. Quote Link to comment Share on other sites More sharing options...
misillsam Posted March 18, 2008 Share Posted March 18, 2008 Someone find THE solution on this insolvable problem, or is better solution to not use this contribution ? Quote Link to comment Share on other sites More sharing options...
Guest Posted March 29, 2008 Share Posted March 29, 2008 Me too same error, never resolved. Replace line 238 with the following, and the error goes away. I haven't tested the rest of the functionality yet, but let me know if this helps. $option_values_query = tep_db_query('select pv.products_options_values_id as id, pv.products_options_values_name as text from '. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv inner join '. TABLE_PRODUCTS_OPTIONS .' po on po.products_options_id=pv.products_options_values_id join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and po.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name'); Jamieson Rhyne Mean Canary Quote Link to comment Share on other sites More sharing options...
misillsam Posted April 2, 2008 Share Posted April 2, 2008 Replace line 238 with the following, and the error goes away. I haven't tested the rest of the functionality yet, but let me know if this helps. $option_values_query = tep_db_query('select pv.products_options_values_id as id, pv.products_options_values_name as text from '. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv inner join '. TABLE_PRODUCTS_OPTIONS .' po on po.products_options_id=pv.products_options_values_id join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and po.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name'); Jamieson Rhyne Mean Canary When line 238- are replaced with this one, no more error on advanced search, all attributes array are created but not POPULATED. all attributes dropdown menus are empty :( Error 1054 still continue on advanced search result. :(( Quote Link to comment Share on other sites More sharing options...
grzebzi Posted April 8, 2008 Share Posted April 8, 2008 When line 238- are replaced with this one, no more error on advanced search, all attributes array are created but not POPULATED.all attributes dropdown menus are empty :( Error 1054 still continue on advanced search result. :(( $option_values_query = tep_db_query('select pv.products_options_values_id, pv.products_options_values_name from ('. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv, '. TABLE_PRODUCTS_OPTIONS .' po) join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and pv.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name ORDER BY products_options_values_name ASC'); should work fine: All You need to add is ( after FROM and ) before join. That should do the thing, works for me. Please give info if worked. Quote Link to comment Share on other sites More sharing options...
amyakir Posted April 13, 2008 Share Posted April 13, 2008 (edited) $option_values_query = tep_db_query('select pv.products_options_values_id, pv.products_options_values_name from ('. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv, '. TABLE_PRODUCTS_OPTIONS .' po) join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and pv.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name ORDER BY products_options_values_name ASC'); should work fine: All You need to add is ( after FROM and ) before join. That should do the thing, works for me. Please give info if worked. Edited April 13, 2008 by amyakir Quote Link to comment Share on other sites More sharing options...
amyakir Posted April 13, 2008 Share Posted April 13, 2008 one small correction to the script submitted by grzebzi, add after pv.products_options_values_name as text that all! Quote Link to comment Share on other sites More sharing options...
amyakir Posted April 15, 2008 Share Posted April 15, 2008 one small correction to the script submitted by grzebzi, add after 'select pv.products_options_values_id as id and after pv.products_options_values_name as text that all! Quote Link to comment Share on other sites More sharing options...
thenewbug Posted September 30, 2008 Share Posted September 30, 2008 i get this error: 1054 - Unknown column 'pv.products_options_values_id' in 'on clause' select pv.products_options_values_id as id, pv.products_options_values_name as text from products_options_values pv, products_options po join products_options_values_to_products_options popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =1 and po.language_id =1 group by pv.products_options_values_id, products_options_values_name [TEP STOP] i installed: Advanced Search - Attributes http://www.oscommerce.com/community/contri...l/search,search and it was working fine yester day (man that sounds soo lame) and today it went a fluke. i think it was something to do with php5 but im not sure since im not very good at php Help Any one.... I am not sure whether this thread is open and people still having problems with 'Advanced Search' after installing contribution: http://www.oscommerce.com/community/contributions,2337 with errors of the type: 1054 - Unknown Column 'p.products_id' In 'on Clause'..... Read on... The problem is the way MySQL5 treats a left join. There is very good contribution about this problem and how to fix it in any PHP file, not just osCommerce: http://www.oscommerce.com/community/contributions,4654 If you are a novice and cannot get it working even after reading the above contribution, then follow what I have done if you like. It worked for me. FIND this piece of code in your current advanced_search.php $option_values_query = tep_db_query('select pv.products_options_values_id as id, pv.products_options_values_name as text from '. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv, '. TABLE_PRODUCTS_OPTIONS .' po join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and po.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name'); $option_values_list = array(); //subzerostudio.com - bug fix and REPLACE it with : $option_values_query = tep_db_query('select pv.products_options_values_id as id, pv.products_options_values_name as text from ('. TABLE_PRODUCTS_OPTIONS_VALUES . ' pv, '. TABLE_PRODUCTS_OPTIONS .' po) join ' . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . ' popv on (pv.products_options_values_id = popv.products_options_values_id) where popv.products_options_id = po.products_options_id and popv.products_options_id =' . $attributes['products_options_id'] . ' and po.language_id ='. (int)$languages_id . ' group by pv.products_options_values_id, products_options_values_name'); $option_values_list = array(); // MySQL5 Fix (Only differences are addition of couple of brackets!) NEXT: in your advanced_search_results.php FIND if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) { $select_str .= ", SUM(tr.tax_rate) as tax_rate "; } $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; $from_str .= "," . TABLE_PRODUCTS_ATTRIBUTES . ' pa'; if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) { if (!tep_session_is_registered('customer_country_id')) { $customer_country_id = STORE_COUNTRY; $customer_zone_id = STORE_ZONE; } $from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')"; } REPLACE it with: if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) { $select_str .= ", SUM(tr.tax_rate) as tax_rate "; } $from_str = "from ((" . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; $from_str .= "," . TABLE_PRODUCTS_ATTRIBUTES . ' pa'; if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) { if (!tep_session_is_registered('customer_country_id')) { $customer_country_id = STORE_COUNTRY; $customer_zone_id = STORE_ZONE; } $from_str = "from ((((" . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c"; $from_str .= "," . TABLE_PRODUCTS_ATTRIBUTES . ' pa'; $from_str .= ")left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id) left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')"; } and that's it. All the errors from both advanced_search.php and advanced_search_result.php have gone! If you are still having problems, I can send you my copies of the above files to compare with yours. (The above is for clean install of v2.2 RCa2) Please give info if it works. 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.