Display Products ONLY from specific Sub category

[sigspace]

hello,

 

i have an oscommerce site which i am trying to show products in a page only from a certain sub category.

 

 

the main category id is '3'

 

the sub category id is '6'

 

 

but when i put either of these codes:

 

$current_category_id = '3_6'

$current_category_id = '3' && $current_categories_id = '6';

 

 

either way it displays all products within the whole main category '3'...and not just products within the sub category.

 

 

it is very frustrating and i am sure i am missing something very simple unfortunately.

 

 

 

i would be VERY grateful if someone can please help me on what needs to be typed in the code to make it display products only from the sub category.

 

 

 

thank you!

[satish]

selcect * from products to categories where categories id = 6.

 

This will give you all product product that specific category.

 

Now using this set of data(products id) you can extract product data from products and products description table.

 

Satish

[sigspace]

hi satish,

thank you for the quick reply. unfortunately i could not see how to insert that detail into my current code. here is my current code:

 

$current_category_id = '3_6';
$select_column_list .= 'p.prod_desc, ';
// show the products of a specified manufacturer
   if (isset($HTTP_GET_VARS['labels_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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1,  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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "' and p.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 . " p.products_id, p.labels_id, p.artists_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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "'";
   $listing_sql = "select " . $select_column_list . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "'  and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$current_category_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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1,  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_LABELS . " l on p.labels_id = l.labels_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.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)) ) {
     $listing_sql .= " order by p.products_Releasedate desc";
   } else {
     $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
     $sort_order = substr($HTTP_GET_VARS['sort'], 1);
    $listing_sql .= " order by p.products_Releasedate desc";
   }
?>

 

 

Your help would be really great and appreciated to solve this problem.

 

 

Thanks!

[satish]

$current_category_id = '3_6';

 

checge this by

$current_category_id = '6';

 

that will do.

 

Satish

 

 

[sigspace]

unfortunately that has not worked either :(

 

when i change the $current_category_id to '6', no products display at all, it is an empty area. For some reason it needs the reference to the main category id number '3' before the sub category id. is very strange

 

 

Would be really great to get this issue fixed, as it is a very strange and frustrating problem.

 

 

 

Thanks!

[satish]

p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$current_category_id . "'";

 

this to

 

p2c.categories_id = '6' and c.parent_id = '"3 "'";

 

in sql query that is getting fired.

 

Satish

[sigspace]

hi Satish,

thank you for the reply.

 

getting closer to fixing this :) i have tried your suggestion, and it has improved and helped the issue, it now only shows the products from a certain sub category '6', which is great...BUT the code is now making the same product appear 5 times in the page results. not sure why this is?...here is the code below:

 

$select_column_list .= 'p.prod_desc, ';
// show the products of a specified manufacturer
   if (isset($HTTP_GET_VARS['labels_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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1,  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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "' and p.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 . " p.products_id, p.labels_id, p.artists_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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "'";
   $listing_sql = "select " . $select_column_list . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['labels_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "'  and p2c.categories_id = '6' and c.categories_id and c.parent_id = '" . (int)$current_category_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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LABELS . " l, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.labels_id = l.labels_id and l.labels_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.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 . " p.products_id, p.labels_id, p.artists_id,p.products_subimage1,  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_LABELS . " l on p.labels_id = l.labels_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_date_start <  '" . date('Y-m-d') . "' and p.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)) ) {
     $listing_sql .= " order by p.products_Releasedate desc";
   } else {
     $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
     $sort_order = substr($HTTP_GET_VARS['sort'], 1);
    $listing_sql .= " order by p.products_Releasedate desc";
   }
?>
<?
//echo $listing_sql;
 $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');
 if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
   <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
   <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?>ffff</td>
 </tr>
</table>
<?php
 }
 $list_box_contents = array();
 if ($listing_split->number_of_rows > 0) {
  $listing_query = tep_db_query($listing_split->sql_query);

 

 

Your help again would be much appreciated.

 

 

Thanks!

[satish]

apply a distinct product_id condition in mysql.

 

Satish

[sigspace]

YES :) brilliant!...putting a "select distinct" query worked perfectly, thank you satish.

 

i was wondering if you could also help me with this block of code below aswell when you get chance, would be great if you could...to do with products tabs:

 

<?php
 $product_info_query = tep_db_query("select  p.labels_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" .$_REQUEST['products_id'] . "' and pd.products_id = p.products_id");
   $product_info = tep_db_fetch_array($product_info_query);
$labels_id = $product_info['labels_id'];
if($labels_id == 3 ||  $_REQUEST['labels_id']==3)
{
?>
 <div id="TabbedPanels1" class="TabbedPanels">
 <ul class="TabbedPanelsTabGroup">
   <li class="TabbedPanelsTab" tabindex="0"><br> Paint</li>
   <li class="TabbedPanelsTab" tabindex="0" onClick="loadTrimmings()"><br> Trimmings</li>
 </ul>
 <div class="TabbedPanelsContentGroup">
   <div class="TabbedPanelsContent" id="labels_paint">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
   </div>

   <div class="TabbedPanelsContent" id="labels_trimmings">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
   </div>
 </div>
</div>
<div id="loadingimagediv" style="display:none;visibility:hidden">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
</div>
<script type="text/javascript">
<!--
var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1",{defaultTab: 0});
//-->
</script>
 <br />
<?php
 }
?>
<div id="TabbedPanels1" class="TabbedPanels">
 <ul class="TabbedPanelsTabGroup">
   <li class="TabbedPanelsTab" tabindex="0"><br> Chairs</li>
   <li class="TabbedPanelsTab" tabindex="0" onClick="loadSofas()"><br> Sofas</li>
 </ul>
 <div class="TabbedPanelsContentGroup">
   <div class="TabbedPanelsContent" id="labels_chairs">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
   </div>

   <div class="TabbedPanelsContent" id="labels_sofas">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
   </div>
 </div>
</div>
<div id="loadingimagediv" style="display:none;visibility:hidden">
<br/><br/>
	<img src="images/loading_ajax.gif" alt="" border="0">
<br/><br/>
</div>
<script type="text/javascript">
<!--
var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1",{defaultTab: 0});
//-->
</script>
<!-- <![endif]-->
</div>
</script>
</td>
</tr>
   </table></td>
<?php
 } else { // default page
?>
<?php
 }
?>
<!-- body_text_eof //-->
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- right_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>
<!-- right_navigation_eof //-->
   </table></td>
 </tr>
</table>
<!-- body_eof //-->
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

 

i am trying to get it so that on the page if the supplier (label id) = '3' then it just displays the tabs saying Paint and Trimmings etc... and NOT the normal tabs display of Chairs and Sofas.

 

The code above is working to display the paint and trimmings tabs if the label id = '3'...BUT it also shows the normal tabs display method underneath aswell, which is not correct.

 

i am sure i have missed something very simple in the code to tell it to not show both displays, just to show the different display if the labels id = '3'

 

 

 

It would be really great if you could help to solve this issue aswell and show what code to add in or take out.

 

 

Thanks!