David Johnson Posted November 1, 2013 Posted November 1, 2013 My site has a total of 72 products, however every individual product is replicated with prices of all of the other products. In other words each single product is listed 72 times and described properly but with 72 different prices, so instead of 72 products with 72 prices there are 72 products with 5184 prices! If any product is selected (Buy it now) with whatever erroneous price is assigned, the correct product details and price appear ready to pass on to the checkout. I've spent hours trying to figure out where this error is but with no success, so any guidance would be very much appreciated.
MrPhil Posted November 1, 2013 Posted November 1, 2013 What's the history of this site? What add-ons? Any multiple price add-ons?
David Johnson Posted November 1, 2013 Author Posted November 1, 2013 What's the history of this site? What add-ons? Any multiple price add-ons? No add ons! History wise it's been running since 2006 without too many problems. Maybe the problem lies with an alteration that was made when the server was changed to one that did not support php 4. The company hosting the site changed some code presumably to make it php 5 compliant and it appeared to be working OK except for this price conundrum. I've contacted the site host many times but emails go unanswered. Trawling through the site the only file that seems to have been altered is the products(catalog)/index.php. If I replace that file with the previous, the whole shopping cart just reads as gobbledegook. I have tried looking at both files to try and spot any glaring error, but nothing seems obvious so perhaps I'm barking up the wrong tree?
David Johnson Posted November 1, 2013 Author Posted November 1, 2013 Apologies for not posting up the modified code! All alterations are in this section of index.php which appear only to change 'p' to 'p2c' so I suppose it must be one of these that is the problem? As a relative dummy to php I cannot see what is wrong..... // We are asked to show only a specific category $listing_sql = "select " . $select_column_list . " p2c.products_id, p.manufacturers_id, p.products_price, 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 . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "' and p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $HTTP_GET_VARS['filter_id'] . "'"; } else { // We show them all $listing_sql = "select " . $select_column_list . " p2c.products_id, p.manufacturers_id, p.products_price, 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 . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'"; } // We build the categories-dropdown // $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p2c.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . $languages_id . "' and p.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name"; } else { // show the products in a given categorie if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) { // We are asked to show only specific catgeory $listing_sql = "select " . $select_column_list . " p2c.products_id, p.manufacturers_id, p.products_price, 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 . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['filter_id'] . "' and p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; } else { // We show them all $listing_sql = "select " . $select_column_list . " p2c.products_id, p.manufacturers_id, p.products_price, 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 p2c.products_id = s.products_id where p.products_status = '1' and p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; } // We build the manufacturers Dropdown // $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p2c.products_id = p2c.products_id and p2c.categories_id = '" . $current_category_id . "' order by m.manufacturers_name"; } if ( (!$HTTP_GET_VARS['sort']) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'],0,1) > sizeof($column_list)) ) { for ($col=0, $n=sizeof($column_list); $col<$n; $col++) { if ($column_list[$col] == 'PRODUCT_LIST_NAME') { $HTTP_GET_VARS['sort'] = $col+1 . 'a'; $listing_sql .= " order by pd.products_name"; break; } } } else { $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1); $sort_order = substr($HTTP_GET_VARS['sort'], 1); $listing_sql .= ' order by '; switch ($column_list[$sort_col-1]) { case 'PRODUCT_LIST_MODEL': $listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; case 'PRODUCT_LIST_NAME': $listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : ''); break; // ########### Products Description Hack begins ########### case 'PRODUCT_LIST_DESCRIPTION': $listing_sql .= "pd.products_description ". ($sort_order == 'd' ? "desc" : ""); break; // ############## End Added ################# case 'PRODUCT_LIST_MANUFACTURER': $listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; case 'PRODUCT_LIST_QUANTITY': $listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; case 'PRODUCT_LIST_IMAGE': $listing_sql .= "pd.products_name"; break; case 'PRODUCT_LIST_WEIGHT': $listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; case 'PRODUCT_LIST_PRICE': $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name"; break; } } ?> <!-- ANM CHANGES BELOW: 1) change td width to * 2) change table width to 95% 3) add align=center to table --> <td width="*" valign="top"> <table border="0" width="95%" align="center" cellspacing="0" cellpadding="0"> <tr> <td class="pageHeading"><br><?php echo HEADING_TITLE; ?><br><br><div class="cathead style1"> </div><BR> </td> <?php // optional Product List Filter if (PRODUCT_LIST_FILTER > 0) { if (isset($HTTP_GET_VARS['manufacturers_id'])) { $filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p2c.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name"; } else { $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " .
David Johnson Posted November 1, 2013 Author Posted November 1, 2013 I have at last found the error not by any coding expertise but by copy and pasting bits of the original index.php file into the modified index.php file, and eventually by trial and error I have the site working again. If ever it is any use to anyone in the future this was the error: // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, 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 p2c.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'"; Both of the p had been written as p2c when changed back the multiple price for each item disappeared.
MrPhil Posted November 2, 2013 Posted November 2, 2013 The reason you needed to do that is that you migrated from MySQL 4 to MySQL 5 at the same time PHP was upgraded. MySQL 5 has some backward incompatibilities with 4 in the precedence handling of table name lists (comma operator) and JOIN operations, to come into conformance with SQL standards. You probably still have some unpatched bugs waiting to bite you. If you are still running an osC 2.2 store, please seriously consider upgrading to 2.3.3. There are a ton of PHP 5 upgrades, MySQL 5 upgrades, bug fixes, and security patches in there. It is possible to do this while preserving all your data. You will need to get and install 2.3-compatible versions of all your add-ons (that you still need), and any custom coding will have to be redone, but in the long run you will be a LOT better off.
David Johnson Posted November 2, 2013 Author Posted November 2, 2013 If you are still running an osC 2.2 store, please seriously consider upgrading to 2.3.3. There are a ton of PHP 5 upgrades, MySQL 5 upgrades, bug fixes, and security patches in there. It is possible to do this while preserving all your data. You will need to get and install 2.3-compatible versions of all your add-ons (that you still need), and any custom coding will have to be redone, but in the long run you will be a LOT better off. Thank you for the advice, I have already downloaded 2.3.3 however I will spend some time reading up on the installation so that I do not make too many mistakes. I will also search the oscommerce fora for hints and pitfalls for this particular installation.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.