Jump to content
Sign in to follow this  
gbm

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

Recommended Posts

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]

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

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  

×