Unable to display linked product in category page

snk060:

To come out of this issue, what I was doing is:

Suppose I have 4 product (p1, p2, p3, p4) and 2 category (cat1, cat2)

Earlier:
Cat 1 (p1, p2, p3, p4) => so it displays (p1, p2, p3, p4)
Cat 2 (all linked product) => so it displays (nothing)

Now:
Cat 1 (p1, p2 + 2 linked pdt) => so it displays (p1, p2)
Cat 2 (p3, p4 + 2 linked pdt) => so it displays (p3, p4)

I did like this to display atleast some product in cat 2. So that Cat 2 will not be completely blank.

But, if I have only one product in category then

Cat 1 (p1) => so it displays (p1)
Cat 2 (linked ptd) => it displays nothing coz of linked pdt.

Okay, so I went back to my previous data and query... Considering that Zen-Cart doesn't force relationships, I reworked the data and the SQL to handle multiple languages, multiple products, and made sure that it could handle a linked product (product_id exists mutliple times with only one category for each instance of the product_id in the products to categories table). This is what I came up with, though I haven't compared it to the previous three provided:

SELECT p.products_type, m.manufacturers_name, pd.products_name
FROM pd, ptc, m, p
WHERE (((p.products_id)=[pd].[product_id] And (p.products_id)=[ptc].[products_id]) AND ((p.manufacturers_id)=[m].[manufacturers_id]) AND ((pd.language_id)=1) AND ((ptc.category_id)=1) AND ((p.products_status)=1));

From the above: p is the products table, m is the manufacturers table, pd is the product description, and ptc is the products to categories table.
In the where statement: ptc.category_id = 1 <- 1 is the current category being referenced (Ie., the category from your query that is defined before this code.)
pd.language_id = 1 where 1 is to be set to the current language.
That should fix your issues now that I have properly considered the categories data to be the products_to_categories table.

The list of parentheses may not be necessary; however, the above works when the tables are properly filled out.

Dataset was this:
two languages
four products
two categories
two manufacturers
two product descriptions for each product (one for each language in pd). Don't have a "language override" in this, if the product description does not exist in the language of choice, then it is not displayed... Apparently to force even a null, the application being used indicated that an additional query would be necessary to then be inserted into this query... I didn't go and develop that.. :/ Supposedly the outer joins necessary to make it work were "too confusing" for the application... I think ideally, would want to query the pd table against the p table on the language of choice, such that all p's were returned and those pd's that were equivalent were provided and NULL for the remaining. Then where NULL is provided to give back the pd in the default language. But as it currently stands, if the pd does not exist in the language of choice (no record in the pd for the language of choice), that row of data is not returned... I don't have multiple language in place on my site, so I can not tell if the existence of a second language forces a product description record for the other language(s) even if no data is in the fields... I am hoping it does force a record for the sake of this query...

p1 and p4 main category was 1 (table p)
p2 and p3 main category was 2 (table p)
p1, p3, p4 were in category 1 (as listed in ptc)
p2, p3, p4 were in category 2 (as listed in ptc)
Therefore can see that products p3 and p4 are linked products between categories 1 and 2, while p1 and p2 are each in their own category alone.
p1 and p4 were manufacturer 1 (table m)
p2 and p3 were manufacturer 2 (table m)
the above query as written provided results for p1, p3, and p4 as expected because looking at the category being category 1.

SELECT p.products_type, p.products_id, pd.products_name, m.manufacturers_name
FROM categories, pd, m, p
``` This is looking for data in tables named pd, m and p, but not in categories, so I'm not sure there is any reason to include categories in the FROM. Also, as written, the tables are *named* pd etc, which is not the case. The statement doesn't provide any alias structure to identify what actual tables the aliases refer to. There are lots of ways to write combined-table queries, and I have sometimes found that one way the manual suggests doesn't work in a particular situation while another way does.

gjh42:

SELECT p.products_type, p.products_id, pd.products_name, m.manufacturers_name
FROM categories, pd, m, p

The example provided in the above code context is from a previous attempt to provide data. 

Regarding the lack of use of table references, that was described in the text. The code delimiter was used. To provide a visual representation. In the basic setup of trying to resolve the issue, table aliases were not used; however, generally speaking the need to add such references was identified. A final product of the code is expected to be posted for those having interest in this topic.

The data being tested is not exported from mySQL, but instead a generic set of tables that contain only the pieces of information needed to make the connections. Initially, categories was being considered as the dataset that was provided into the function in question, the use of the reference to categories after the from statement forced the catgories "table" to be identified.  Again, that was wrong also.

Because the OP has demonstrated a functional knowledge of SQL, I have not gone through the added steps to provide the coded version that would definitely work without alteration. That's a political way of stating that I took the easy way, because it looked like I could.  That said, when I went back to see what problems were caused by that code through the OP's input and I thought about what I had setup, I realized that the tables I setup for testing did not accurately include the intermediate table of products to categories (the table used to identify that a product is linked). My more recent provision of the query (which also does not include aliases) does properly incorporate the products to categories table; however, it depends on an entry in the product description table for all languages that are being considered.  That entry can be blank, but it must exist in order for the query to return a result on that product.

Ah... as a schematic query structure rather than an actual finished query, it makes sense. Carry on:)

gjh42:

Ah... as a schematic query structure rather than an actual finished query, it makes sense. Carry on:)

Don't get me wrong your input is appreciated and valued. As I pointed out, the query so far provided depends on having data in the database for all languages expected to be searched. It seems to me that ZC typically handles missing info by defaulting back to the default language, but this particular query doesn't have that in it. Ie, there is no null returned if the product description for language 2 if the language 2 product description is not at least instantiated in the table.

Yeah, we're like half way between pseudo code and functional code. :)

mc12345678:

Okay, so I went back to my previous data and query... Considering that Zen-Cart doesn't force relationships, I reworked the data and the SQL to handle multiple languages, multiple products, and made sure that it could handle a linked product (product_id exists mutliple times with only one category for each instance of the product_id in the products to categories table). This is what I came up with, though I haven't compared it to the previous three provided:

SELECT p.products_type, m.manufacturers_name, pd.products_name
FROM pd, ptc, m, p
WHERE (((p.products_id)=[pd].[product_id] And (p.products_id)=[ptc].[products_id]) AND ((p.manufacturers_id)=[m].[manufacturers_id]) AND ((pd.language_id)=1) AND ((ptc.category_id)=1) AND ((p.products_status)=1));

> 
> From the above: p is the products table, m is the manufacturers table, pd is the product description, and ptc is the products to categories table.
> In the where statement: ptc.category_id = 1 <- 1 is the current category being referenced (Ie., the category from your query that is defined before this code.) 
> pd.language_id = 1 where 1 is to be set to the current language.
> That should fix your issues now that I have properly considered the categories data to be the products_to_categories table.
> 
> The list of parentheses may not be necessary; however, the above works when the tables are properly filled out.
> 
> Dataset was this:
> two languages
> four products 
> two categories
> two manufacturers
> two product descriptions for each product (one for each language in pd).  Don't have a "language override" in this, if the product description does not exist in the language of choice, then it is not displayed...  Apparently to force even a null, the application being used indicated that an additional query would be necessary to then be inserted into this query... I didn't go and develop that.. :/  Supposedly the outer joins necessary to make it work were "too confusing" for the application... I think ideally, would want to query the pd table against the p table on the language of choice, such that all p's were returned and those pd's that were equivalent were provided and NULL for the remaining.  Then where NULL is provided to give back the pd in the default language.  But as it currently stands, if the pd does not exist in the language of choice (no record in the pd for the language of choice), that row of data is not returned...  I don't have multiple language in place on my site, so I can not tell if the existence of a second language forces a product description record for the other language(s) even if no data is in the fields...  I am hoping it does force a record for the sake of this query...
> 
> p1 and p4 main category was 1 (table p)
> p2 and p3 main category was 2 (table p)
> p1, p3, p4 were in category 1 (as listed in ptc)
> p2, p3, p4 were in category 2 (as listed in ptc)
> Therefore can see that products p3 and p4 are linked products between categories 1 and 2, while p1 and p2 are each in their own category alone.
> p1 and p4 were manufacturer 1 (table m)
> p2 and p3 were manufacturer 2 (table m)
> the above query as written provided results for p1, p3, and p4 as expected because looking at the category being category 1.


Thanks MC12345678!!,

I changed code as you suggest, but now I am getting blank display for cat 1 also. I am not understanding where I am getting wrong. Below is the code what I have edited:

```php
<?php 	

	if (!defined('IS_ADMIN_FLAG')) {
	
	  die('Illegal Access');
	
	}
	
	
	// Assuming that you have a very limited number of categories and products. You would not want to list all on a single page if you have too many anyway
	// Putting all in a single array is not a good way to use memory, but for a very limited number of entries this is fast and easy
	
	$categories_order_by = ""; // Set categories' display order. i.e:  sort by categories_name
	$products_order_by = "ORDER BY products_date_added";
	$categories_limit = ""; // Set categories' display order. i.e:  sort by categories_name
	$products_limit = "";
	// Please do not edit below this line
	
						 
	
	$categories_query_raw = "SELECT * FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd WHERE cd.categories_id = c.categories_id AND c.categories_status = 1 AND c.parent_id != 0 AND c.parent_id = $current_category_id
																											AND cd.language_id = :languageID ".$categories_order_by;
	$categories_query_raw = $db->bindVars($categories_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');	

	

	
	$categories_result = $db->Execute($categories_query_raw);
	$categories = array();
	$current_cat = 0;
	
	
	while (!$categories_result->EOF) {
		$categories[] = $categories_result->fields;
		// query the products belong to each specific cat, attach them into the array
		$products_query_raw = "SELECT p.products_type, p.products_id, pd.products_name, pd.products_no_tab, p.products_image, p.products_price, p.products_tax_class_id, ptc.products_id, ptc.categories_id, p.manufacturers_id, m.manufacturers_id,
                                    p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call,
                                    p.product_is_always_free_shipping, p.products_qty_box_status
                             FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc,
                             " . TABLE_MANUFACTURERS . " m, ". TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." 
							 AND p.products_status = 1
							 AND ptc.categories_id = 1
							 AND p.manufacturers_id = m.manufacturers_id
                             AND p.products_id = pd.products_id
							 AND p.products_id = ptc.products_id
                             AND pd.language_id = :languageID " . $products_order_by;
		$products_query_raw = $db->bindVars($products_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');
		$products_result = $db->Execute($products_query_raw);		
		while (!$products_result->EOF){
			$categories[$current_cat]['products'][]  = $products_result->fields;
			$products_result->MoveNext();
		}		
		$categories_result->MoveNext();
		$current_cat++;
	}
	
	
	if ($current_cat > 0 ) {
		require($template->get_template_dir('tpl_modules_all_cats_all_products.php', DIR_WS_TEMPLATE, $current_page_base,'templates'). '/' . 'tpl_modules_all_cats_all_products.php');
	}
 
?>

snk060:

Thanks MC12345678!!,

I changed code as you suggest, but now I am getting blank display for cat 1 also. I am not understanding where I am getting wrong. Below is the code what I have edited:

<?php if (!defined('IS_ADMIN_FLAG')) { die('Illegal Access'); } // Assuming that you have a very limited number of categories and products. You would not want to list all on a single page if you have too many anyway // Putting all in a single array is not a good way to use memory, but for a very limited number of entries this is fast and easy $categories_order_by = ""; // Set categories' display order. i.e: sort by categories_name $products_order_by = "ORDER BY products_date_added"; $categories_limit = ""; // Set categories' display order. i.e: sort by categories_name $products_limit = ""; // Please do not edit below this line $categories_query_raw = "SELECT * FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd WHERE cd.categories_id = c.categories_id AND c.categories_status = 1 AND c.parent_id != 0 AND c.parent_id = $current_category_id AND cd.language_id = :languageID ".$categories_order_by; $categories_query_raw = $db->bindVars($categories_query_raw, ':languageID', $_SESSION['languages_id'], 'integer'); $categories_result = $db->Execute($categories_query_raw); $categories = array(); $current_cat = 0; while (!$categories_result->EOF) { $categories[] = $categories_result->fields; // query the products belong to each specific cat, attach them into the array $products_query_raw = "SELECT p.products_type, p.products_id, pd.products_name, pd.products_no_tab, p.products_image, p.products_price, p.products_tax_class_id, ptc.products_id, ptc.categories_id, p.manufacturers_id, m.manufacturers_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc, " . TABLE_MANUFACTURERS . " m, ". TABLE_PRODUCTS_DESCRIPTION . " pd WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." AND p.products_status = 1 AND ptc.categories_id = 1 AND p.manufacturers_id = m.manufacturers_id AND p.products_id = pd.products_id AND p.products_id = ptc.products_id AND pd.language_id = :languageID " . $products_order_by; $products_query_raw = $db->bindVars($products_query_raw, ':languageID', $_SESSION['languages_id'], 'integer'); $products_result = $db->Execute($products_query_raw); while (!$products_result->EOF){ $categories[$current_cat]['products'][] = $products_result->fields; $products_result->MoveNext(); } $categories_result->MoveNext(); $current_cat++; } if ($current_cat > 0 ) { require($template->get_template_dir('tpl_modules_all_cats_all_products.php', DIR_WS_TEMPLATE, $current_page_base,'templates'). '/' . 'tpl_modules_all_cats_all_products.php'); } ?>

I haven't tested your version of the SQL, but it looks to me like it would cause a problem. The where clause of yours, contains the following "extra" comparisons which I think are causing your issue in the cycle provided.

WHERE p.master_categories_id = ".$categories_result->fields['categories_id']."

AND ptc.categories_id = 1

From the testing that I did and the goal explained, I believe the WHERE statement should be as follows                             

WHERE ptc.category_id = " . $categories_result->fields['categories_id'] . " AND p.products_status = 1 And p.manufacturers_id = m.manufacturers_id AND p.products_id = pd.product_id AND p.products_id = ptc.products_id AND pd.language_id = :languageID " . $products_order_by;

Otherwise, the where portion provided puts too many constraints on the data and will leave out information.

mc12345678:

I haven't tested your version of the SQL, but it looks to me like it would cause a problem. The where clause of yours, contains the following "extra" comparisons which I think are causing your issue in the cycle provided.

WHERE p.master_categories_id = ".$categories_result->fields['categories_id']."

AND ptc.categories_id = 1

> 
> From the testing that I did and the goal explained, I believe the WHERE statement should be as follows                             
> 
> ```
WHERE ptc.category_id = " . $categories_result->fields['categories_id'] . "  AND p.products_status = 1 And p.manufacturers_id = m.manufacturers_id  AND p.products_id = pd.product_id AND p.products_id = ptc.products_id AND pd.language_id = :languageID " . $products_order_by;

Otherwise, the where portion provided puts too many constraints on the data and will leave out information.

To explain a little why I say that the "extra" clauses would be a problem is this: the fact that looking for categories = 1 means that only looking for a single category, but the thought is that in the end, every category that is available is to be considered to find products that are in some way related to the current category that is being searched.
Using p.master_categories_id instead of ptc.categories_id will result in a list of items that does not include the linked items although on testing resulted in a response with the same number of items as there would be for the number of items including linked items, but one of the results was the same as a previous result. In the loop, this result in replacing the data returned before and therefore would not show the linked item(s). To resolve that, the ptc.categories_id is needed to be used so that it returns a different product_id and thus the linked item(s). Hope that helps a little. I plan on posting the final code as soon as I am at a computer instead of on a mobile device.

mc12345678:

To explain a little why I say that the "extra" clauses would be a problem is this: the fact that looking for categories = 1 means that only looking for a single category, but the thought is that in the end, every category that is available is to be considered to find products that are in some way related to the current category that is being searched.
Using p.master_categories_id instead of ptc.categories_id will result in a list of items that does not include the linked items although on testing resulted in a response with the same number of items as there would be for the number of items including linked items, but one of the results was the same as a previous result. In the loop, this result in replacing the data returned before and therefore would not show the linked item(s). To resolve that, the ptc.categories_id is needed to be used so that it returns a different product_id and thus the linked item(s). Hope that helps a little. I plan on posting the final code as soon as I am at a computer instead of on a mobile device.

<?php     

    if (!defined('IS_ADMIN_FLAG')) {
    
      die('Illegal Access');
    
    }
    
    
    // Assuming that you have a very limited number of categories and products. You would not want to list all on a single page if you have too many anyway
    // Putting all in a single array is not a good way to use memory, but for a very limited number of entries this is fast and easy
    
    $categories_order_by = ""; // Set categories' display order. i.e:  sort by categories_name
    $products_order_by = "ORDER BY products_date_added";
    $categories_limit = ""; // Set categories' display order. i.e:  sort by categories_name
    $products_limit = "";
    // Please do not edit below this line
    
                         
    
    $categories_query_raw = "SELECT * FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd WHERE cd.categories_id = c.categories_id AND c.categories_status = 1 AND c.parent_id != 0 AND c.parent_id = $current_category_id
                                                                                                            AND cd.language_id = :languageID ".$categories_order_by;
    $categories_query_raw = $db->bindVars($categories_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');    

    

    
    $categories_result = $db->Execute($categories_query_raw);
    $categories = array();
    $current_cat = 0;
    
    
    while (!$categories_result->EOF) {
        $categories[] = $categories_result->fields;
        // query the products belong to each specific cat, attach them into the array

        $products_query_raw = "SELECT p.products_type, p.products_id, pd.products_name, pd.products_no_tab, p.products_image, p.products_price, p.products_tax_class_id, 
                                    p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call,
                                    p.product_is_always_free_shipping, p.products_qty_box_status
                             FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc,
                             " . TABLE_MANUFACTURERS . " m, ". TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE ptc.categories_id = " . $categories_result->fields['categories_id'] . "  
                             AND p.products_status = 1 
                             AND p.manufacturers_id = m.manufacturers_id  
                             AND p.products_id = pd.product_id 
                             AND p.products_id = ptc.products_id 
                             AND pd.language_id = :languageID " . $products_order_by;
        $products_query_raw = $db->bindVars($products_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');
        $products_result = $db->Execute($products_query_raw);        
        while (!$products_result->EOF){
            $categories[$current_cat]['products'][]  = $products_result->fields;
            $products_result->MoveNext();
        }        
        $categories_result->MoveNext();
        $current_cat++;
    }
    
    
    if ($current_cat > 0 ) {
        require($template->get_template_dir('tpl_modules_all_cats_all_products.php', DIR_WS_TEMPLATE, $current_page_base,'templates'). '/' . 'tpl_modules_all_cats_all_products.php');
    }
 
?>

Extra code found: ****ptc.products_id, ptc.categories_id, p.manufacturers_id, m.manufacturers_id, **** Unless there is a need to use this data downstream, then this is not needed. In the example I provided, I did not create a full set of data tables and needed to be sure what was being returned, so I had included these or something similar. p.products_id is already returned which in this search will be the same value as ptc.products_id so, this is extra unnecessary information. ptc.categories_id appears to not be necessary as the array $categories[$current_cat]['products'][] basically reassigns these products to the category number $current_cat (number from 0 to last category of $categories_result). p.manufacturers_id will be the same as m.manufacturers_id, and are used only for reference between the two when performing a query (ie., something like a primary key) If there is a reason for manufacturers_id then suggest returning only one of them.

Also, I noticed that the query of my previous post referenced ptc.category_id instead of ptc.categories_id... That is corrected above. Hopefully this works for you. As I said, it should return $categories[0] through $categories[x] with each category containing all of the products (linked included) that fit into that category for that language provided that product is described in that language. Let me know the results of the above code, or that it has been implemented on the page that was in question.

Hi Mc12345678,

Using above code I got error message:

1054 Unknown column 'pd.product_id' in 'where clause'
in:
[SELECT p.products_type, p.products_id, pd.products_name, pd.products_no_tab, p.products_image, p.products_price, p.products_tax_class_id, pd.products_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status FROM products p, products_to_categories ptc, manufacturers m, products_description pd WHERE ptc.categories_id = 43 AND p.products_status = 1 AND p.manufacturers_id = m.manufacturers_id AND p.products_id = pd.product_id AND p.products_id = ptc.products_id AND pd.language_id = 1 ORDER BY products_date_added]

but after correcting pd.product_id to pd.products_id, still I am not getting product display in category pages.

Thanks!!

snk060:

Hi Mc12345678,

Using above code I got error message:

1054 Unknown column 'pd.product_id' in 'where clause'
in:
[SELECT p.products_type, p.products_id, pd.products_name, pd.products_no_tab, p.products_image, p.products_price, p.products_tax_class_id, pd.products_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status FROM products p, products_to_categories ptc, manufacturers m, products_description pd WHERE ptc.categories_id = 43 AND p.products_status = 1 AND p.manufacturers_id = m.manufacturers_id AND p.products_id = pd.product_id AND p.products_id = ptc.products_id AND pd.language_id = 1 ORDER BY products_date_added]

> 
> 
> but after correcting pd.product_id to pd.products_id, still I am not getting product display in category pages.
> 
> Thanks!!

No products or no linked products? The only thing that has really changed it seems is the where clause which should be returning more items than the previous version. Nothing has changed in the template file that displays the data? 

What values does tpl_modules_all_cats_all_products.php need in order to display information?

What happens if you add print_r($categories) at the end of this section:

```php
while (!$products_result->EOF){
$categories[$current_cat]['products'][] = $products_result->fields;
 $products_result->MoveNext();
} 

I thouught I had gone through each of my stores tables and the field names one by one, but apparently I did not catch that one.

Do you need to add ptc.products_id, ptc.categories_id, p.manufacturers_id, m.manufacturers_id back in to the returned fields?

Both Products and linked-products are not being displayed.

Below is the php code in tpl_modules_all_cats_all_products.php

<?php
	define('NUM_PRODUCT_PER_ROW', 4); // This is where you set number of products per row
?>






<div id="basic-accordian">

	<?php for($j = 0; $j < sizeof($categories); $j++){ ?>


		<div id="test-header" class="accordion_headings header_highlight">

			<?php echo '<a name="'.$categories[$j]['categories_name'].'">'.'</a>' ?>
			<?php echo $categories[$j]['categories_name']; ?>

		</div>
		<div id="category_content_<?php echo $j; ?>">
			<div class="accordion_child">
			<?php //query thru each product
			$product_counter = 0;
			$products = $categories[$j]['products'];
			$numProductsInThisCategory = ceil(sizeof($products));
			$numRows = ($numProductsInThisCategory / NUM_PRODUCT_PER_ROW);
			$columns = (NUM_PRODUCT_PER_ROW);

				?>

			<table width="730" cellspacing="10" cellpadding="0" border="0" align="center"><tbody><tr>

					<?php for($i =0; $i < NUM_PRODUCT_PER_ROW && $product_counter < sizeof($products); $i++){ ?>

						<?php if($i%$columns == 0)
							echo '<tr>';
						?>

						<!-- This is where the code to display a single product is located, style it as you want -->
						<td width="160" style="padding-right: 15px; float: left;" >
						<div class="products_image">

							<?php if($product_counter < $numProductsInThisCategory){

								echo '<a href="' . zen_href_link(zen_get_info_page($products[$product_counter]['products_id']), 'products_id=' . $products[$product_counter]['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $products[$product_counter]['products_image'], $products[$product_counter]['products_name'], IMAGE_PRODUCT_LISTING_WIDTH, IMAGE_PRODUCT_LISTING_HEIGHT) . '</a>';
							} ?>

						</div>

						<div class="products_name">

							<?php if($product_counter < $numProductsInThisCategory){
								echo '<a href="' . zen_href_link(zen_get_info_page($products[$product_counter]['products_id']), 'products_id=' . $products[$product_counter]['products_id']) . '">' . $products[$product_counter]['products_name']. '</a>';
							}
							 ?>
						</div>

						<div class="products_tab">

							<?php if($product_counter < $numProductsInThisCategory){
								echo $products[$product_counter]['products_no_tab'];
							}
							 ?>
						</div>

						<div class="products_price">

							<?php

							$number = $products[$product_counter]['products_price']; // It's a string from a DB in real life
							$formatted_number = round($number,2);

							if($product_counter < $numProductsInThisCategory){
								echo 'List Price $'. $formatted_number;
							}
							 ?>
						</div>

						<div class="member_price">

							<?php

							$number = $products[$product_counter]['products_price']; // It's a string from a DB in real life
							$formatted_number2 = round(($number - ($number * 10)/100),2);

							if($product_counter < $numProductsInThisCategory){
								echo 'Member Price $'. $formatted_number2;
							}
							 ?>
						</div>








					<!--	<div class="products_buynow">

							<?php if($product_counter < $numProductsInThisCategory){
								echo '<a href="' . zen_href_link($_GET['main_page'], zen_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products[$product_counter]['products_id']) . '">' . zen_image_button(BUTTON_IMAGE_BUY_NOW, BUTTON_BUY_NOW_ALT, 'class="listingBuyNowButton"') . '</a>';
							}
							 ?>
						</div> -->




						</td>

						<?php if(($i+1)%$columns == 0 || $i == ($numProductsInThisCategory-1))
							echo '</tr>';
						?>


					<?php
					$product_counter++;
					?>

					<?php }	?>

					</tbody></table>

			</div>
		</div>
	<?php
	} ?>
</div>

After adding print_r($categories) at the end of section :

Sent you in pm!

Do you need to add ptc.products_id, ptc.categories_id, p.manufacturers_id, m.manufacturers_id back in to the returned fields?

No, It is not required.

Thank you!!

snk060:

Both Products and linked-products are not being displayed.

Below is the php code in tpl_modules_all_cats_all_products.php

<?php define('NUM_PRODUCT_PER_ROW', 4); // This is where you set number of products per row ?> <div id="basic-accordian">

<?php for($j = 0; $j < sizeof($categories); $j++){ ?>


	<div id="test-header" class="accordion_headings header_highlight">

		<?php echo '<a name="'.$categories[$j]['categories_name'].'">'.'</a>' ?>
		<?php echo $categories[$j]['categories_name']; ?>

	</div>
	<div id="category_content_<?php echo $j; ?>">
		<div class="accordion_child">
		<?php //query thru each product
		$product_counter = 0;
		$products = $categories[$j]['products'];
		$numProductsInThisCategory = ceil(sizeof($products));
		$numRows = ($numProductsInThisCategory / NUM_PRODUCT_PER_ROW);
		$columns = (NUM_PRODUCT_PER_ROW);

			?>

		<table width="730" cellspacing="10" cellpadding="0" border="0" align="center"><tbody><tr>

				<?php for($i =0; $i < NUM_PRODUCT_PER_ROW && $product_counter < sizeof($products); $i++){ ?>

					<?php if($i%$columns == 0)
						echo '<tr>';
					?>

					<!-- This is where the code to display a single product is located, style it as you want -->
					<td width="160" style="padding-right: 15px; float: left;" >
					<div class="products_image">

						<?php if($product_counter < $numProductsInThisCategory){

							echo '<a href="' . zen_href_link(zen_get_info_page($products[$product_counter]['products_id']), 'products_id=' . $products[$product_counter]['products_id']) . '">' . zen_image(DIR_WS_IMAGES . $products[$product_counter]['products_image'], $products[$product_counter]['products_name'], IMAGE_PRODUCT_LISTING_WIDTH, IMAGE_PRODUCT_LISTING_HEIGHT) . '</a>';
						} ?>

					</div>

					<div class="products_name">

						<?php if($product_counter < $numProductsInThisCategory){
							echo '<a href="' . zen_href_link(zen_get_info_page($products[$product_counter]['products_id']), 'products_id=' . $products[$product_counter]['products_id']) . '">' . $products[$product_counter]['products_name']. '</a>';
						}
						 ?>
					</div>

					<div class="products_tab">

						<?php if($product_counter < $numProductsInThisCategory){
							echo $products[$product_counter]['products_no_tab'];
						}
						 ?>
					</div>

					<div class="products_price">

						<?php

						$number = $products[$product_counter]['products_price']; // It's a string from a DB in real life
						$formatted_number = round($number,2);

						if($product_counter < $numProductsInThisCategory){
							echo 'List Price $'. $formatted_number;
						}
						 ?>
					</div>

					<div class="member_price">

						<?php

						$number = $products[$product_counter]['products_price']; // It's a string from a DB in real life
						$formatted_number2 = round(($number - ($number * 10)/100),2);

						if($product_counter < $numProductsInThisCategory){
							echo 'Member Price $'. $formatted_number2;
						}
						 ?>
					</div>








				<!--	<div class="products_buynow">

						<?php if($product_counter < $numProductsInThisCategory){
							echo '<a href="' . zen_href_link($_GET['main_page'], zen_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products[$product_counter]['products_id']) . '">' . zen_image_button(BUTTON_IMAGE_BUY_NOW, BUTTON_BUY_NOW_ALT, 'class="listingBuyNowButton"') . '</a>';
						}
						 ?>
					</div> -->




					</td>

					<?php if(($i+1)%$columns == 0 || $i == ($numProductsInThisCategory-1))
						echo '</tr>';
					?>


				<?php
				$product_counter++;
				?>

				<?php }	?>

				</tbody></table>

		</div>
	</div>
<?php
} ?>

</div> ``` > > > After adding print_r($categories) at the end of section : > > Sent you in pm! > > > > > > No, It is not required. > > Thank you!!

Haven't reviewed the above code, but the thing is, previously you had results, but weren't receiving linked results. Now nothing is printing, but data is comming back.

Looks like things are being returned, just not "good enough" to display what is needed by template. Didn't "easily" see that any one product showed twice, but I did not do a detailed review. Sorry.

any suggestion plz?

snk060:

any suggestion plz?

Okay, not sure that this gives the exact results you were looking for, because what it looks like is that I get back all the categories in my store, including the products that exist in each category as if I were navigating the categories individually. I have commented out the areas that were causing a problem.

I don't have a manufacturer for every item, so that was causing no data to be returned in some cases.

    $categories_query_raw = "SELECT * FROM ".TABLE_CATEGORIES." c, ".TABLE_CATEGORIES_DESCRIPTION." cd WHERE cd.categories_id = c.categories_id AND c.categories_status = 1 " /*AND c.parent_id != 0 "*/ . /*AND c.parent_id = $current_category_id                                                                                                        */"AND cd.language_id = :languageID ".$categories_order_by;
//    print($categories_query_raw);
    $categories_query_raw = $db->bindVars($categories_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');    
//    print($categories_query_raw);

    $categories_result = $db->Execute($categories_query_raw);
    $categories = array();
    $current_cat = 0;

    while (!$categories_result->EOF) {
        $categories[] = $categories_result->fields;
        // query the products belong to each specific cat, attach them into the array

        $products_query_raw = "SELECT p.products_type, p.products_id, pd.products_name, " . /*pd.products_no_tab, */"p.products_image, p.products_price, p.products_tax_class_id,
                                    p.products_date_added, " ./*m.manufacturers_name, */"p.products_model, p.products_quantity, p.products_weight, p.product_is_call,
                                    p.product_is_always_free_shipping, p.products_qty_box_status
                             FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc,
                             " . /*TABLE_MANUFACTURERS . " m, ". */TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE ptc.categories_id = " . $categories_result->fields['categories_id'] . "  
                             AND p.products_status = 1
                             " . /*AND p.manufacturers_id = m.manufacturers_id*/"  
                             AND p.products_id = pd.products_id
                             AND p.products_id = ptc.products_id
                             AND pd.language_id = :languageID " . $products_order_by;
        $products_query_raw = $db->bindVars($products_query_raw, ':languageID', $_SESSION['languages_id'], 'integer');
        $products_result = $db->Execute($products_query_raw);        
        while (!$products_result->EOF){
            $categories[$current_cat]['products'][]  = $products_result->fields;
          $products_result->MoveNext();
        }        
        $categories_result->MoveNext();
        $current_cat++;
    }

    if ($current_cat > 0 ) {

After this point, I modified a few things so that I could confirm that linked products would show. It all came down to what $categories[] contains...

Great!! Thank you so much!! You are a Genius!!

Earlier It was displaying all category, sub-category and products (both linked and direct) in one page but after removing comment from /AND c.parent_id != 0 "/ . /*AND c.parent_id = $current_category_id */ it solved my problem.

One again thank you so much, you solved my biggest problem!!

snk060:

Great!! Thank you so much!! You are a Genius!!

Earlier It was displaying all category, sub-category and products (both linked and direct) in one page but after removing comment from /AND c.parent_id != 0 "/ . /*AND c.parent_id = $current_category_id */ it solved my problem.

One again thank you so much, you solved my biggest problem!!

Are you sure that you had to uncomment "/AND c.parent_id != 0 "/"?
I'm not sure how you're data is setup now or in the future, but I had a majority of my data fit that one criteria, so I was getting some results, but none provided seemed or were linked. Issue with my data is that many are in a category off of the store's root, and therefore only those categories that were a sub category showed up it seemed like. Maybe it was different, I don't recall, I just wanted to maximize the results to prove that the query designed worked. Some things that would make the query more successful would be to left join the other tables (manufacturer if needed, and product description) for product description fields, if they come back as null then would want to pull the produuct description fields for the default language.that way, if a product doesn't have a description in the language of choice, at least it appears and the customer may have to do independent reseaarch or ask you what it is in their language.

mc12345678:

Are you sure that you had to uncomment "/AND c.parent_id != 0 "/"?
I'm not sure how you're data is setup now or in the future, but I had a majority of my data fit that one criteria, so I was getting some results, but none provided seemed or were linked. Issue with my data is that many are in a category off of the store's root, and therefore only those categories that were a sub category showed up it seemed like. Maybe it was different, I don't recall, I just wanted to maximize the results to prove that the query designed worked. Some things that would make the query more successful would be to left join the other tables (manufacturer if needed, and product description) for product description fields, if they come back as null then would want to pull the produuct description fields for the default language.that way, if a product doesn't have a description in the language of choice, at least it appears and the customer may have to do independent reseaarch or ask you what it is in their language.

Yes!! after uncommenting "/AND c.parent_id != 0 "/" it worked.

In my store my of the products are category off, many of them are linked product.

I will update that in my live server and will send you the link.

Thank you so much!!!