Help with Products Extra Fields Contrib, Can ANyone Help

[Dan Coates]

All,

 

I'm having some problems getting my products extra fields contribution to work, I can install and use it fine. using manual install on virgin OSC MS2 shop. However, when try searching for anything, not just extra field content I get this error:

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_description pd, categories c, products_to_categories p2c 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 ((pd.products_name like '%matrox%' or p.products_model like '%matrox%' or m.manufacturers_name like '%matrox%' or p2pef.products_extra_fields_value like '%matrox%') )

 

[TEP STOP]

 

 

If I use the original advanced_search_result.php the search works but obviously doesn?t search the extra fields, this is the code that?s altered in the advanced_search_result.php:

 

The installation file says to replace this line:

 

$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";

 

The above line does not exist in OSC MS2?

 

with

 

// START: Extra Fields Contribution
 $from_str = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.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";
// END: Extra Fields Contribution

 

However, there are two lines which makeup the above lines and these are:

 

[u]$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";[/u]

 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 . "')";
 }  [u]$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";[/u]

 

 

Does it mean replace both the underlined lines with the one in the installation file, or delete both of these lines and replace them with the piece of code out of the installation file? Surely that will effect the script, as each line is from a different section?

 

 

The second part that has to be altered is this

 

instead of:

 

$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";

 

The install says place this:

 

// START: Extra Fields Contribution

$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%' or p2pef.products_extra_fields_value like '%" . tep_db_input($keyword) . "%'";

// END: Extra Fields Contribution

 

Is there anything wrong with this code?

 

AS I said everything else works perfectly, just the problems with the coding for the search featurer. This contribution was tried multiple times on a virgin install of OSC MS2.

 

Hope somebody can help.

 

Regards,

 

Dan

[Dan Coates]

I forgot to remove the and from the code block near the top of my post when I wrote this post, I did not include those in the file when I tried to install it, so I still have a problem (that was not it).

 

Dan

[Guest]

I forgot to remove the and from the code block near the top of my post when I wrote this post, I did not include those in the file when I tried to install it, so I still have a problem (that was not it).

 

Dan

 

I am having the above problem as well. Any help would be appreciated. Also the new fields display above everything else when viewing the product regardless of sort order put in. They show in the right place in preview when entering product.

[rkr31]

Same problem here. Anyone found a resolution yet?

[xinirnix]

I think this is the solution to your(s) problems :

(it was posted by frost77 on feb 06 2006 - [Contribution] Extra Fields, Unknown error Options - http://www.oscommerce.com/forums/index.php?sho...c=118357&st=40)

 

QUOTE(wyszukaj @ Jan 31 2006, 07:39 PM)

 

Hello!

 

I have instaled new version of contribution and recived this error message:

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_description pd, categories c, products_to_categories p2c 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 ((pd.products_name like '%dualhead%' or p.products_model like '%dualhead%' or m.manufacturers_name like '%dualhead%' or p2pef.products_extra_fields_value like '%dualhead%' or pd.products_description like '%dualhead%') )

 

I don't now what i'm doing wrong. Please help me.

http://www.czekoladki.com

 

 

 

I had the same problem. Comparing the old and new advanced_search_result.php files, there is an extra line which is not needed. If you remove this, it works. Here is how to find it:

 

Open the advanced_search_result.php file from the contribution.

 

Search for the following text:

 

// START: Extra Fields Contribution

// $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 = "from (" . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . " p2pef on p.products_id=p2pef.products_id) left join " . TABLE_MANUFACTURERS . " m on m.manufacturers_id=p.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";

// END: Extra Fields Contribution

 

 

 

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 . "')";

}

 

$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

The very last line adds tables to $from_str that are already there, so you can safely remove it. That is, erase the following:

 

$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

And resave. It should then work.