PHP Fatal error: MySQL error 1066: Not unique table/alias: 'p2c'

[TamyA]

Any idea why i am getting this error? I searched hi and low for it and i did not see it happened with anyone else uses Zen Cart.

[18-Jan-2025 06:20:33 UTC] Request URI: /index.php?main_page=index&manufacturers_id=8&sort=3a&page=2, IP address: x.x.13.12, Language id 1
#0 [internal function]: zen_debug_error_handler()
#1 /includes/classes/db/mysql/query_factory.php(733): trigger_error()
#2 /includes/classes/db/mysql/query_factory.php(678): queryFactory->show_error()
#3 /includes/classes/db/mysql/query_factory.php(307): queryFactory->set_error()
#4 /includes/templates/responsive_classic/templates/tpl_index_product_list.php(46): queryFactory->Execute()
#5 /includes/modules/pages/index/main_template_vars.php(232): require('/home/user/...')
#6 /includes/templates/responsive_classic/common/tpl_main_page.php(181): require('/home/user/...')
#7 /index.php(94): require('/home/user/...')

--> PHP Fatal error: MySQL error 1066: Not unique table/alias: 'p2c' :: SELECT p.products_image, pd.products_name, p.products_quantity, p.products_id, p.products_type, p.master_categories_id,

p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description,
IF(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price,
IF(s.status = 1, s.specials_new_products_price, p.products_price) AS final_price,
p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
FROM products p
LEFT JOIN specials s ON s.products_id = p.products_id
LEFT JOIN products_description pd ON pd.products_id = p.products_id AND pd.language_id = 1
LEFT JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id
LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id
WHERE p.products_status = 1
AND p.products_quantity > 0
AND m.manufacturers_id = 8 AND p2c.categories_id = p.master_categories_id AND m.manufacturers_id = 8 AND p2c.categories_id = p.master_categories_id
ORDER BY p.products_price_sorter , pd.products_name ==> (as called by) /includes/templates/responsive_classic/templates/tpl_index_product_list.php on line 46 <== in /includes/classes/db/mysql/query_factory.php on line 733.

[balihr]

Any idea why i am getting this error? I searched hi and low for it and i did not see it happened with anyone else uses Zen Cart.

LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id

Your query is doubling the left join.
Check the query here: /includes/modules/pages/index/main_template_vars.php and see if it's been modified and remove the double line, should be fine.

[swguy]

Also check:

includes/index_filters/default_filter.php
includes/index_filters/YOUR_TEMPLATE/default_filter.php

[TamyA]

Your query is doubling the left join.
Check the query here: /includes/modules/pages/index/main_template_vars.php and see if it's been modified and remove the double line, should be fine.

Thanks for the reply, The file was not edited, still the same file that was uploaded from zen cart 2.1.0

[TamyA]

Also check:

includes/index_filters/default_filter.php
includes/index_filters/YOUR_TEMPLATE/default_filter.php

Thanks for the reply, This all the left join i found in includes/index_filters/default_filter.php and all of the is withen if else statment,
so they should not be double lines, should they?

This the code fro the file and the LEFT_JOIN in red
// show the products of a specified manufacturer
if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
// We show them all
$and .= " AND m.manufacturers_id = " . (int)$_GET['manufacturers_id'] . " ";
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only a specific category
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= " AND p2c.categories_id = " . (int)$_GET['filter_id'] . " ";
} else {
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= ' AND p2c.categories_id = p.master_categories_id ';
}
} else {
if (empty($and) && !empty($current_category_id)) {
// show the products in a given category
// We show them all
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= " AND p2c.categories_id = " . (int)$current_category_id . " ";
}
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only specific category
$and .= " AND m.manufacturers_id = " . (int)$_GET['filter_id'] . " ";
}
}
.
.
.
.
// show the products of a specified manufacturer
if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
// We show them all
$and .= " AND m.manufacturers_id = " . (int)$_GET['manufacturers_id'] . " ";
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only a specific category
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= " AND p2c.categories_id = " . (int)$_GET['filter_id'] . " ";
} else {
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= ' AND p2c.categories_id = p.master_categories_id ';
}
} else {
if (empty($and) && !empty($current_category_id)) {
// show the products in a given category
// We show them all
$sql_joins .= " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id ";
$and .= " AND p2c.categories_id = " . (int)$current_category_id . " ";
}
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// We are asked to show only specific category
$and .= " AND m.manufacturers_id = " . (int)$_GET['filter_id'] . " ";
}
}



the only thing was added in this file is code in red below

$listing_sql .= $sql_joins ?? ' ';
$where_str = "
WHERE p.products_status = 1
AND p.products_quantity > 0
" . $and . "
" . $alpha_sort;


includes/index_filters/YOUR_TEMPLATE/default_filter.php, i do not have YOUR_TEPLATE under index_filters

[balihr]

Is this actually code that's in your default_filter.php file? Why is the same block of code doubled?

[TamyA]

Is this actually code that's in your default_filter.php file? Why is the same block of code doubled?

Sorry, my mistake, it seems like when i edited the post to add more info i pasted the code again.

[swguy]

@balihr: It's a bit confusing (especially to read unformatted) but I don't think the code is doubled, those should be mutually exclusive if/else blocks.

@TamyA: Did you check the files I suggested?

[TamyA]

@TamyA: Did you check the files I suggested?
includes/index_filters/YOUR_TEMPLATE/default_filter.php

There is no files in includes/index_filters/YOUR_TEMPLATE/default_filter.php
since there are no directory /your _template/ inside include/index_filters


the only thing was added in includes/index_filters/default_filter.php is the code in red below

$listing_sql .= $sql_joins ?? ' ';
$where_str = "
WHERE p.products_status = 1
AND p.products_quantity > 0
" . $and . "
" . $alpha_sort;

[swguy]

Sorry, can't reproduce.