Unable to display linked product in category page

Hi,

I am using all category all product module for category page but I am unable to display linked product in category page. Can any one help me out with this issue, please. Below are the php code I am using for that.

Thanks!!

$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
                             LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                             WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." 
							 AND p.products_status = 1
                             AND p.products_id = pd.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:

Hi,

I am using all category all product module for category page but I am unable to display linked product in category page. Can any one help me out with this issue, please. Below are the php code I am using for that.

Thanks!!

$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
                         LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                         WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." 
                         AND p.products_status = 1
                         AND p.products_id = pd.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');
}

Unsure, what is the issue? Ie what is happening that you are not expecting, or what is not happening that you are expecting?  Have you displayed any of the arrays that you have to see if/what is/is not getting set? (print_r($arrayname);)

Thanks for your response mc12345678!!

I am sending you my website link through pm.

Thanks!

So from the PM received, are you saying that the same code is in two different places, in one it is working in the other it is not?

Not exactly, Actually in the second link that I have provided displays the direct product and under second link products are linked.

Since, products under second link is direct product so all products are being display in master category page but products under first link is linked product so it is not displaying in the category page. I wanted to know that what should I edit in above code so that linked product can also be displayed in the master category page.

Thankyou!!

snk060:

Not exactly, Actually in the second link that I have provided displays the direct product and under second link products are linked.

Since, products under second link is direct product so all products are being display in master category page but products under first link is linked product so it is not displaying in the category page. I wanted to know that what should I edit in above code so that linked product can also be displayed in the master category page.

Thankyou!!

And you do already have a link established for the main product to the new category? (Data is in the products_to_categories table such that product_id is listed more than once, each occurrence with a different category_id and the category_id matches the category in which it should be displayed?)

mc12345678:

And you do already have a link established for the main product to the new category? (Data is in the products_to_categories table such that product_id is listed more than once, each occurrence with a different category_id and the category_id matches the category in which it should be displayed?)

Correct!! Any idea how can I achive this.

Thanks!

snk060:

Hi,

I am using all category all product module for category page but I am unable to display linked product in category page. Can any one help me out with this issue, please. Below are the php code I am using for that.

Thanks!!

$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
                         LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                         WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." 
						 AND p.products_status = 1
                         AND p.products_id = pd.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 notice that $current_cat is set to 0 and then incremented by 1 as the loop progresses, why is it not set to the master category number of the $categories_result? How is the variable $categories later used? Is it incrementally processed, or is the category number for your desired category used in $categories[category_of_choice]?  I ask because on my initial look for the included template. File I did not see that template fileso I don't know how the code is used later.

snk060:

Correct!! Any idea how can I achive this.

Thanks!

I'm sorry, to ensure there is not a communication issue/potential language barrier, when asking how to achieve "this" what is it that you are asking to achieve?

mc12345678:

I notice that $current_cat is set to 0 and then incremented by 1 as the loop progresses, why is it not set to the master category number of the $categories_result? How is the variable $categories later used? Is it incrementally processed, or is the category number for your desired category used in $categories[category_of_choice]? I ask because on my initial look for the included template. File I did not see that template fileso I don't know how the code is used later.

I asked why not set to master category, but I may have meant category number. I'm still not sure which would be correct at the moment. My head is kind of spinning. I originally wrote category I'd, the I changed my mind, now I'm unsure which it should be. :/

Sorry I was not clear!! Achive in the seance to display linked product in master category page.

Thanks!

snk060:

Hi,

I am using all category all product module for category page but I am unable to display linked product in category page. Can any one help me out with this issue, please. Below are the php code I am using for that.

Thanks!!

$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
                         LEFT JOIN " . TABLE_MANUFACTURERS . " m ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd
                         WHERE p.master_categories_id = ".$categories_result->fields['categories_id']." 
                         AND p.products_status = 1
                         AND p.products_id = pd.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');
}

Huh, it may be as simple as adding the word AS between your table name and the alias that you have given it for the cases where the table is aliased? I would have thought an error would have resulted instead of just no data... Do you have error logs associated with running/accessing the above?

Here is what I would change based on that:

```php
FROM " . TABLE_PRODUCTS . " AS p 
                             LEFT JOIN " . TABLE_MANUFACTURERS . " AS m ON (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " AS pd 

The suggested changes are in green.

This is also a case where displaying the results along the way will help determine the problem. But, I have not found anything that says the aliasing will/can occur in mySQL if the word AS is left out. I could be wrong though. Wouldn't be the first, won't be the last.

Otherwise, the logic seems correct, even though I keep going over it to validate my assumptions.

I looked at the source code of the page that is not working, and it would appear that the $current_cat is gone through one at a time and not based off of the value associated with any particular category (meaning that I think the use of $current_cat++; is correct.)

FROM " . TABLE_PRODUCTS . " AS p 
``` MySQL does not want the AS in this location; the alias should immediately follow the table name.

FROM " . TABLE_PRODUCTS . " p

You would use AS if you want to alias the field being selected. ```
"SELECT p.products_type AS ptype,...

$current_cat is used just as a way to insert more data under the current main field of $categories without incrementing the main field: ```php
$categories[$current_cat]['products'][] = $products_result->fields;

$categories[] starts out with a value of 0 and is numerically incremented, just like $current_cat: ```php
$categories[] = $categories_result->fields;

gjh42:

FROM " . TABLE_PRODUCTS . " AS p

> ```
FROM " . TABLE_PRODUCTS . " p 

You would use AS if you want to alias the field being selected. ```
"SELECT p.products_type AS ptype,...

From: <http://dev.mysql.com/doc/refman/5.0/en/select.html>

And not to say that it is untrue that AS is needed or would be used for a field reference. It appears as either is acceptable with or without the AS on the table reference. 
> A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:
> 
> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
>   WHERE t1.name = t2.name;
> 
> SELECT t1.name, t2.salary FROM employee t1, info t2
>   WHERE t1.name = t2.name;

Okay, points taken:) I was going by the conventions I have seen everywhere in Zen Cart usage, and as far as I recall from MySQL tutorials.

gjh42:

Okay, points taken:) I was going by the conventions I have seen everywhere in Zen Cart usage, and as far as I recall from MySQL tutorials.

No problem, I was trying to research the format of the SQL to verify that as written it wouldn't cause an error; however, on that note... I broke down and used a commercial product to in essence provide the SQL query that would give what seems to be the desired result. I came up with at least two different queries, depending on how relationships were drawn.

The table categories is being defined as a "table" that contains the category_id and other category information. So, I would think that the FROM categories could go away because the data is found/compared to via the previous array. All that said though, these two are formatted differently/constructed differently than the previous query. That may be why there is no result. But regarding below, I don't believe that I have entered enough sample data to accurately identify which of the two queries should be used... (Both queries return the same information with the current dataset tested.

No table relationships:

SELECT p.products_type, p.products_id, pd.products_name, m.manufacturers_name
FROM categories, pd, m, p
WHERE (((categories.category_id)=[p].[master_categories_id]) AND ((p.products_status)=1) AND ((pd.ID)=[p].[products_id]) AND ((p.manufacturers_id)=[m].[manufacturers_id]));

Relationship designated between m.manufacturers_id and p.manufacturers_id

SELECT p.products_type, p.products_id, pd.products_name, m.manufacturers_name
FROM categories, pd, m INNER JOIN p ON m.manufacturers_id = p.manufacturers_id
WHERE (((categories.category_id)=[p].[master_categories_id]) AND ((p.products_status)=1) AND ((pd.ID)=[p].[products_id]) AND ((p.manufacturers_id)=[m].[manufacturers_id]));

With a relationship added to the second query above where a one-to-one relationship exists between the categories array returned and the product's master categories:

SELECT p.products_type, p.products_id, pd.products_name, m.manufacturers_name
FROM pd, categories INNER JOIN (m INNER JOIN p ON m.manufacturers_id = p.manufacturers_id) ON categories.category_id = p.master_categories_id
WHERE (((categories.category_id)=[p].[master_categories_id]) AND ((p.products_status)=1) AND ((pd.ID)=[p].[products_id]) AND ((p.manufacturers_id)=[m].[manufacturers_id]));

Got the same result as the previous two queries... Again small amount of data, no real extra data in one set or another, other than I have four products, two master categories, two manufacturers, and product descriptions for only two of the four products... Etc... etc.... Etc... Recommend trying at least one of these to see if better results are returned...

Great idea mc12345678!!

But getting problem when category carries only one product.

snk060:

Great idea mc12345678!!

But getting problem when category carries only one product.

What problem is occurring when $categories only has one item?

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.