Jump to content
Sign in to follow this  
NewtoRock

Advanced Search - Attributes

Recommended Posts

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....

Share this post


Link to post
Share on other sites

This is a Mysql5 related bug.

Search forum for mysql5 patches apply those.

 

Satish


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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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. :((

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
$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 by amyakir

Share this post


Link to post
Share on other sites

one small correction to the script submitted by grzebzi, add after pv.products_options_values_name as text

that all!

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×