Kes Posted August 14, 2006 Share Posted August 14, 2006 My server has upgraded and my shop no longer works. At first i couldn't click on a product and find more details so i replaced the product_info.php and all works well. I have made the recommended fixes to index.php and advanced_search_results.php and fixed the admin section. yet i am still getting an error when i click add to cart button I am using multi_list_column contribution error reads 1054 - Unknown column 'p.products_id' in 'on clause' select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri ce8_qty, p.products_qty_blocks, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = '162' and pd.products_id = '162' and pd.language_id = '1' Can anyone please help with this. I have search and search for an answer. cheers Link to comment Share on other sites More sharing options...
jhsands Posted August 14, 2006 Share Posted August 14, 2006 Did you run the included SQL query on your database? Make sure you run that query... BACKUP your current files before editing. Did I mention to BACKUP your current files, first? "I'm not a hero, I'm a firefighter; it is my job to save lives. I'm a Jesus Christ Firefighter saving souls from the flames!" Installed contribs: Almost XP Buttons *** Attribute Sort *** Auto Thumbnail Change *** Column Product Listing (for SPPC v4.0) *** Contact Us Registered *** Country-State Selector 1.3.3J *** CC# Db Mask 1.3 *** Email Order Clickable Link *** Extra Images *** Linkpoint API CVM *** Loginbox Best *** New Spiders *** New Attrib Mgr v.5.0 w/ New Attrib Include *** Multi Product Update *** MySQL Cron *** Pricing per Category *** Product Listing in Columns v2.2 [later upgraded to CPL(SPPC)] *** Product Sort v1.6 *** Seperate Pricing Per Customer v4.15 *** Simple Down for Maintenance [Gokou] *** Ultimate_SEO_URLs_v2.2.2 *** UPS Worldship Export 1.3 *** Welcome Email username & password Link to comment Share on other sites More sharing options...
Kes Posted August 14, 2006 Author Share Posted August 14, 2006 Did you run the included SQL query on your database? Make sure you run that query... Yes i have and still get the same error Any ideas Link to comment Share on other sites More sharing options...
umc Posted August 14, 2006 Share Posted August 14, 2006 I see you're using product price break... there's an issue with a querry if you have mysql 5. On the page of the contribution there was a fix posted. I'll copy-paste it here. it's only a matter of paranthesis. I don't think it's the same sql that bugs you, but it looks similar, and I had the same problem after upgrade, and this fixed it. You can try and see if you figure out the problem by comparing... here's the content of the upgrade fix. hope it helps: ---------------------------------- MySQL5 Compatibility fix for Quantity Price Breaks Per Product By: Alexander Spannenburg (02-05-2006) Original contribution url: http://www.oscommerce.com/community/contributions,1242 INSTALLATION: QUICK AND EASY: 1. Copy PriceFormatter.php from the zipfile to \catalog\includes\classes\ and overwrite the old file. 2. Done MANUAL: 1. In PriceFormatter.php, in the loadProduct() function (line 165), find: $sql="select pd.products_name, p.products_model, p.products_image, p.products_id," . " p.manufacturers_id, p.products_price, p.products_weight," . " p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," . " p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri ce8_qty," . " p.products_qty_blocks," . " p.products_tax_class_id," . " IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price," . " IF(s.status, s.specials_new_products_price, p.products_price) as final_price" . " from " . TABLE_PRODUCTS_DESCRIPTION . " pd," . " " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id," . " " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id" . " where p.products_status = '1'" . " and p.products_id = '" . (int)$product_id . "'" . " and pd.products_id = '" . (int)$product_id . "'" . " and pd.language_id = '". (int)$language_id ."'"; 2. Replace with: $sql="SELECT pd.products_name, p.products_model, p.products_image, p.products_id," . " p.manufacturers_id, p.products_price, p.products_weight," . " p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," . " p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri ce8_qty," . " p.products_qty_blocks," . " p.products_tax_class_id," . " IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price," . " IF(s.status, s.specials_new_products_price, p.products_price) AS final_price" . " FROM ((" . TABLE_PRODUCTS_DESCRIPTION . " pd," . " " . TABLE_PRODUCTS . " p) LEFT JOIN " . TABLE_MANUFACTURERS . " m ON p.manufacturers_id = m.manufacturers_id," . " " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) LEFT JOIN " . TABLE_SPECIALS . " s ON p.products_id = s.products_id" . " WHERE p.products_status = '1'" . " AND p.products_id = '" . (int)$product_id . "'" . " AND pd.products_id = '" . (int)$product_id . "'" . " AND pd.language_id = '". (int)$language_id ."'"; 3. Done Link to comment Share on other sites More sharing options...
Kes Posted August 14, 2006 Author Share Posted August 14, 2006 I see you're using product price break... there's an issue with a querry if you have mysql 5. On the page of the contribution there was a fix posted. I'll copy-paste it here. it's only a matter of paranthesis. I don't think it's the same sql that bugs you, but it looks similar, and I had the same problem after upgrade, and this fixed it. You can try and see if you figure out the problem by comparing... here's the content of the upgrade fix. hope it helps: ---------------------------------- MySQL5 Compatibility fix for Quantity Price Breaks Per Product By: Alexander Spannenburg (02-05-2006) Original contribution url: http://www.oscommerce.com/community/contributions,1242 INSTALLATION: QUICK AND EASY: 1. Copy PriceFormatter.php from the zipfile to \catalog\includes\classes\ and overwrite the old file. 2. Done MANUAL: 1. In PriceFormatter.php, in the loadProduct() function (line 165), find: $sql="select pd.products_name, p.products_model, p.products_image, p.products_id," . " p.manufacturers_id, p.products_price, p.products_weight," . " p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," . " p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri ce8_qty," . " p.products_qty_blocks," . " p.products_tax_class_id," . " IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price," . " IF(s.status, s.specials_new_products_price, p.products_price) as final_price" . " from " . TABLE_PRODUCTS_DESCRIPTION . " pd," . " " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id," . " " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id" . " where p.products_status = '1'" . " and p.products_id = '" . (int)$product_id . "'" . " and pd.products_id = '" . (int)$product_id . "'" . " and pd.language_id = '". (int)$language_id ."'"; 2. Replace with: $sql="SELECT pd.products_name, p.products_model, p.products_image, p.products_id," . " p.manufacturers_id, p.products_price, p.products_weight," . " p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," . " p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri ce8_qty," . " p.products_qty_blocks," . " p.products_tax_class_id," . " IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price," . " IF(s.status, s.specials_new_products_price, p.products_price) AS final_price" . " FROM ((" . TABLE_PRODUCTS_DESCRIPTION . " pd," . " " . TABLE_PRODUCTS . " p) LEFT JOIN " . TABLE_MANUFACTURERS . " m ON p.manufacturers_id = m.manufacturers_id," . " " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) LEFT JOIN " . TABLE_SPECIALS . " s ON p.products_id = s.products_id" . " WHERE p.products_status = '1'" . " AND p.products_id = '" . (int)$product_id . "'" . " AND pd.products_id = '" . (int)$product_id . "'" . " AND pd.language_id = '". (int)$language_id ."'"; 3. Done Cheers i have had to do a complete upgrade on my site so it is now up and running although i have had to do alot of fixing . Thanks for your help Link to comment Share on other sites More sharing options...
rolex911 Posted August 15, 2006 Share Posted August 15, 2006 Can you tell me how you fixed the error on advanced_search_results.php?? My host also suddenly upgraded and broke my site. Smokey San Francisco Link to comment Share on other sites More sharing options...
Kes Posted August 16, 2006 Author Share Posted August 16, 2006 Can you tell me how you fixed the error on advanced_search_results.php?? My host also suddenly upgraded and broke my site. Smokey San Francisco This is where i found some answers. See last link http://oscommerce.com/ext/update-20051112.html good luck Link to comment Share on other sites More sharing options...
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.