"Hide products and categories from groups" error 1052 - Column 'products_id' in from clause is ambiguous

[gbm 0]

We have been using "Separate Pricing Per Customer" and "QPBPP for SPPC".

After adding "Hide products and categories from groups" we are getting the following errors

 

at index.php:

1052 - Column 'products_id' in from clause is ambiguous

 

select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price, pd.products_name from products p, products_description pd left join products_to_categories p2c using(products_id) left join categories c using(categories_id) where p.products_id = pd.products_id and products_status = '1' and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0 and pd.language_id = '1' order by p.products_date_added desc limit 6

 

[TEP STOP]

 

at products_new.php

1052 - Column 'products_id' in from clause is ambiguous

 

select count(*) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd left join products_to_categories p2c using(products_id) left join categories c using(categories_id) where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0

 

[TEP STOP]

 

at product_info.php

1052 - Column 'products_id' in from clause is ambiguous

 

select count(*) as total from products p, products_description pd left join products_to_categories p2c using (products_id) left join categories c using(categories_id) where p.products_status = '1' and p.products_id = '112' and pd.products_id = p.products_id and pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0

 

[TEP STOP]

[Jan Zonjee 57]

After adding "Hide products and categories from groups" we are getting the following errors

 

at index.php:

Try products_description pd left join products_to_categories p2c on pd.products_id = p2c.products_id instead of products_description pd left join products_to_categories p2c using(products_id)

[gbm 0]

There turned out to be more problem files than I thought.

But based on your suggestion I went through the problem files and replaced using(products_id) with on pd.products_id = p2c.products_id

I am thankful for your suggestion since this fixed most of the problem pages so that they now display without an error message.

 

However I have a few new problems.

 

(1)

when I made the correction on also_purchased_products.php I now have a new error:

1054 - Unknown column 'pd.products_id' in 'on clause'

 

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p left join products_to_categories p2c on pd.products_id = p2c.products_id left join categories c using(categories_id) where opa.products_id = '141' and opa.orders_id = opb.orders_id and opb.products_id != '141' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' and find_in_set('1', products_hide_from_groups) = 0 and find_in_set('1', categories_hide_from_groups) = 0 group by p.products_id order by o.date_purchased desc limit 6

 

[TEP STOP]

 

(2)

When logged into the site as a wholesale customer I am seeing retail prices on the main page index.php in the area called "What's New Here?"

I believe it is using catalog/includes/boxes/whats_new.php there.

 

 

(3)

And also when logged into the site as a wholesale customer and viewing product details on product_info.php I still see the retail price over the top of the description.

However the wholesale price shows properly below with the item description.

[Jan Zonjee 57]

However I have a few new problems.

 

(1)

when I made the correction on also_purchased_products.php I now have a new error:

Since you are now joining products and products_to_categories you have to use products p left join products_to_categories p2c on p.products_id = p2c.products_id

 

(2)

When logged into the site as a wholesale customer I am seeing retail prices on the main page index.php in the area called "What's New Here?"

I believe it is using catalog/includes/boxes/whats_new.php there.

Probably a little mistake in adding the SPPC code or you have a template that uses other code and the SPPC code need tweaking. The same goes for your point 3.

[gbm 0]

Thank you again for your help.

 

Replacing on pd.products_id = p2c.products_id with on p.products_id = p2c.products_id in also_purchased_products.php fixed my problem (1).

 

Problem (2) turned out to be catalog/includes/modules/new_products.php

The problem turned out to be when I installed "QPBPP for SPPC"

The installation instructions said to replace Line 51:

$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."'");

with

$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' and customers_group_price != null");

I am not sure why this is even need by "QPBPP for SPPC" but I changed it back and it now displays the prices correctly.

 

Problem (3) I may leave alone for now since this allows the customer to see the full retail price above and his wholesasle prices below.

If I get complaints I will fix it later.

[Jan Zonjee 57]

I am not sure why this is even need by "QPBPP for SPPC" but I changed it back and it now displays the prices correctly.

Maybe your MySQL version has a problem with != null and would like you to use IS NOT NULL instead?

 

The customers_group_price can be null in the QPBPP for SPPC because you can also set the quantity blocks and minimum quantity amount for a product but that does not necessarily mean you need to set the customer group price.

[gbm 0]

Looks like you are correct again.

I replaced != null with IS NOT NULL and it works correctly.