gbm Posted September 15, 2010 Posted September 15, 2010 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] Quote
Jan Zonjee Posted September 15, 2010 Posted September 15, 2010 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) Quote
gbm Posted September 16, 2010 Author Posted September 16, 2010 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. Quote
Jan Zonjee Posted September 16, 2010 Posted September 16, 2010 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. Quote
gbm Posted September 17, 2010 Author Posted September 17, 2010 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. Quote
Jan Zonjee Posted September 17, 2010 Posted September 17, 2010 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. Quote
gbm Posted September 21, 2010 Author Posted September 21, 2010 Looks like you are correct again. I replaced != null with IS NOT NULL and it works correctly. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.