DataMouse Posted July 20, 2008 Share Posted July 20, 2008 Hi all I have a store that has a page for products (as you would expect). The product page allows you to select a category and then the sub category and/or products appear beneath. The issue is that this is not happening correctly. Check the site here. You can see (in the top right "what we offer" box) that the product counts are correct, but it lists all products with incorrect product_id numbers (rollover and see the addresses). I believe this is because of the SQl statement being used to populate the page. It is NOT a template issue. The code for the part of the page concerned is below. Can anyone help please? <table border="0" width="100%" cellpadding="0" cellspacing="0" > <tr> <td height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <td height="20"><div style='height:20px; padding-right:10px;color:#E7A73D; font-weight:bold; font-size:14px; text-align:right;'><img src='images/sign_what_newg.gif' width='21' height='17' style='margin-top:1px;'> WHAT WE OFFER</div></td> </tr> <tr> <td height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <td ><?php include(DIR_WS_BOXES . 'categories.php'); ?></td> </tr> <tr> <td height="1" background="images/hpoint.gif" align="left"><img src="images/hpoint.gif" width="3" height="1" alt=""></td> </tr> <tr> <?php if ($category_depth == 'nested') { $category_query = tep_db_query("select cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id . "' and cd.language_id = '" . (int)$languages_id . "'"); $category = tep_db_fetch_array($category_query); ?> <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td class="pageHeading"><?php echo HEADING_TITLE; ?></td> <td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . $category['categories_image'], $category['categories_name'], HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td> </tr> </table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <?php if (isset($cPath) && strpos('_', $cPath)) { // check to see if there are deeper categories within the current category $category_links = array_reverse($cPath_array); for($i=0, $n=sizeof($category_links); $i<$n; $i++) { $categories_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'"); $categories = tep_db_fetch_array($categories_query); if ($categories['total'] < 1) { // do nothing, go through the loop } else { $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name"); break; // we've found the deepest category the customer is in } } } else { $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name"); } $number_of_categories = tep_db_num_rows($categories_query); $rows = 0; while ($categories = tep_db_fetch_array($categories_query)) { $rows++; $cPath_new = tep_get_path($categories['categories_id']); $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%'; echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n"; if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) { echo ' </tr>' . "\n"; echo ' <tr>' . "\n"; } } // needed for the new products module shown below $new_products_category_id = $current_category_id; ?> </tr> </table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <tr> <td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td> </tr> </table></td> </tr> </table></td> <?php } elseif ($category_depth == 'products' || isset($HTTP_GET_VARS['manufacturers_id'])) { // create column list $define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL, 'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME, 'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER, 'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE, 'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY, 'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT, 'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE, 'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW); asort($define_list); $column_list = array(); reset($define_list); while (list($key, $value) = each($define_list)) { if ($value > 0) $column_list[] = $key; } $select_column_list = ''; for ($i=0, $n=sizeof($column_list); $i<$n; $i++) { switch ($column_list[$i]) { case 'PRODUCT_LIST_MODEL': $select_column_list .= 'p.products_model, '; break; case 'PRODUCT_LIST_NAME': $select_column_list .= 'pd.products_name, '; break; case 'PRODUCT_LIST_MANUFACTURER': $select_column_list .= 'm.manufacturers_name, '; break; case 'PRODUCT_LIST_QUANTITY': $select_column_list .= 'p.products_quantity, '; break; case 'PRODUCT_LIST_IMAGE': $select_column_list .= 'p.products_image, '; break; case 'PRODUCT_LIST_WEIGHT': $select_column_list .= 'p.products_weight, '; break; } } // show the products of a specified manufacturer if (isset($HTTP_GET_VARS['manufacturers_id'])) { if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) { // 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 = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$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 = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"; } } 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 = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'"; } } if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) { for ($i=0, $n=sizeof($column_list); $i<$n; $i++) { if ($column_list[$i] == 'PRODUCT_LIST_NAME') { $HTTP_GET_VARS['sort'] = $i+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; 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; } } ?> <td width="100%" valign="top" align="right"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td align="left" width="100%" valign="top"> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td width="100%" align="right" valign="top"> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td><?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?></td> </tr> </table> </td> </tr> </table> </td> </tr> </table> <?php } else { // default page ?> <td width="495" valign="top" align="center"> <br /> <span class="crimson">Choose the category to see products!</span> </td> <?php } ?> <!-- body_text_eof //--> </tr> </table> I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
DataMouse Posted July 20, 2008 Author Share Posted July 20, 2008 Can anyone help? You can see what I mean on the page. Nitric Oxide upplements shows 5 products. But when clicked on, it lists over 15 pages - and all the product links are the same (SEO friendly). I'm really stuck guys - and need some help! Thanks I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
DataMouse Posted July 21, 2008 Author Share Posted July 21, 2008 Are there any helpers for this? Even a point in the right direction will do folks. PLEASE! :-/ I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
Jan Zonjee Posted July 22, 2008 Share Posted July 22, 2008 The issue is that this is not happening correctly. Perhaps? // 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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'"; Link to comment Share on other sites More sharing options...
DataMouse Posted July 22, 2008 Author Share Posted July 22, 2008 Perhaps? // 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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'"; Thanks Jan - you're a star for replying! This piece of SQL sits in a nested IF statement. Is the IF statement correct? The reason I ask is that, when the category is shown, it has the right number of products listed at the end of the category text - but when clicked, the SQL statement is populating different product data/images. Sorry to be a bit blonde (and sorry to all the blondies in here). Thanks I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
Jan Zonjee Posted July 22, 2008 Share Posted July 22, 2008 The reason I ask is that, when the category is shown, it has the right number of products listed at the end of the category text - but when clicked, the SQL statement is populating different product data/images. Check for the p2c.products_id = p2c.products_id. I spot it in two more places I think. That might be the only problem. Link to comment Share on other sites More sharing options...
DataMouse Posted July 22, 2008 Author Share Posted July 22, 2008 Check for the p2c.products_id = p2c.products_id. I spot it in two more places I think. That might be the only problem. I can see: p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id That doesn't look right to me. Maybe just: pd.products_id = p2c.products_id Is that correct? I'm not great with SQL (I learned with MS Access, so T-SQL isn't a forte!) Thanks again I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
Jan Zonjee Posted July 22, 2008 Share Posted July 22, 2008 I can see: p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id That doesn't look right to me. Is that correct? No, it should be (two or three occurrences): p.products_id = p2c.products_id Link to comment Share on other sites More sharing options...
DataMouse Posted July 22, 2008 Author Share Posted July 22, 2008 Oh - Gotcha: p2c.products_id = p2c.products_id and pd.products_id = p2c.products_id Should be p.products_id = p2c.products_id and pd.products_id = p2c.products_id I had a blind date last night. Her name was ..:. :.: :: .. ::. :. ::. Black Web 2.0 Buttons Blue Orb Web 2.0 Buttons Mac "Aqua" Buttons Black Gloss Buttons Red Gloss Buttons (with metal edging) Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.