Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Advanced Search - Attributes


Guest

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

Link to comment
Share on other sites

  • 1 month later...
  • 4 weeks later...
  • 2 weeks later...
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

Link to comment
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. :((

Link to comment
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.

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

  • 5 months later...
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...