I am trying to add in another table into an already working query but I am getting an error for some reason. I'm not sure if the query is enough to go on since it is being run by another function but I think it might just be my query..
我试图将另一个表添加到已经工作的查询中，但由于某种原因我收到错误。我不确定查询是否足以继续，因为它是由另一个函数运行但我认为它可能只是我的查询..
Here is the original query that works:
这是有效的原始查询：
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_model,
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
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_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 . "'";
And here is the new query with the the added table and where clause (TABLE_PRODUCTS_ATTRIBUTES is the new table pa):
这是带有添加的表和where子句的新查询（TABLE_PRODUCTS_ATTRIBUTES是新表pa）：
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_model,
p.manufacturers_id, p.products_price, pa.products_values_id, 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, " .
TABLE_PRODUCTS_ATTRIBUTES . " pa
left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_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_id = p2c.products_id and p.products_id =
pa.products_id and pd.products_id = p2c.products_id and pd.language_id = '" .
(int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
Is there anything wrong with my query straight away?
我的查询是否有任何问题？
2 个解决方案
#1
3
The problem is that you are mixing the comma-style join syntax with the JOIN keyword. Because they have different precedence, the joins are not being performed in the order you expected (you probably expected left-to-right order).
问题是您将逗号样式的连接语法与JOIN关键字混合在一起。因为它们具有不同的优先级，所以连接不按您期望的顺序执行（您可能期望从左到右的顺序）。
The MySQL manual specifically warns about mixing the two types of join:
MySQL手册特别警告混合两种类型的连接：
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.
但是，逗号运算符的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等。如果在存在连接条件时将逗号连接与其他连接类型混合，则可能会出现'on子句'中未知列'col_name'形式的错误。
A simple change you could make to fix the error is to switch the order of TABLE_PRODUCTS_ATTRIBUTES and TABLE_PRODUCTS:
您可以修复错误的一个简单更改是切换TABLE_PRODUCTS_ATTRIBUTES和TABLE_PRODUCTS的顺序：
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_ATTRIBUTES . " pa, "
. TABLE_PRODUCTS . " p
However this doesn't solve the real problem - that your query is unmaintainable. Better is to change all your comma-style joins to use the JOIN keyword. This will require rewriting your entire query from scratch but it will make it much easier to modify in the future.
但是，这并不能解决实际问题 - 您的查询无法维护。更好的是更改所有逗号样式的连接以使用JOIN关键字。这将需要从头开始重写整个查询，但这将使将来更容易修改。
#2
0
Try changing that line in your query:
尝试更改查询中的该行：
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_ATTRIBUTES . " pa
To:
至：
from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_ATTRIBUTES . " pa)
The parentheses will unsure the order in which the tables are joined is the order you expect.
括号将不确定表连接的顺序是您期望的顺序。
#1
3
The problem is that you are mixing the comma-style join syntax with the JOIN keyword. Because they have different precedence, the joins are not being performed in the order you expected (you probably expected left-to-right order).
问题是您将逗号样式的连接语法与JOIN关键字混合在一起。因为它们具有不同的优先级，所以连接不按您期望的顺序执行（您可能期望从左到右的顺序）。
The MySQL manual specifically warns about mixing the two types of join:
MySQL手册特别警告混合两种类型的连接：
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.
但是，逗号运算符的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等。如果在存在连接条件时将逗号连接与其他连接类型混合，则可能会出现'on子句'中未知列'col_name'形式的错误。
A simple change you could make to fix the error is to switch the order of TABLE_PRODUCTS_ATTRIBUTES and TABLE_PRODUCTS:
您可以修复错误的一个简单更改是切换TABLE_PRODUCTS_ATTRIBUTES和TABLE_PRODUCTS的顺序：
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_ATTRIBUTES . " pa, "
. TABLE_PRODUCTS . " p
However this doesn't solve the real problem - that your query is unmaintainable. Better is to change all your comma-style joins to use the JOIN keyword. This will require rewriting your entire query from scratch but it will make it much easier to modify in the future.
但是，这并不能解决实际问题 - 您的查询无法维护。更好的是更改所有逗号样式的连接以使用JOIN关键字。这将需要从头开始重写整个查询，但这将使将来更容易修改。
#2
0
Try changing that line in your query:
尝试更改查询中的该行：
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_ATTRIBUTES . " pa
To:
至：
from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_ATTRIBUTES . " pa)
The parentheses will unsure the order in which the tables are joined is the order you expect.
括号将不确定表连接的顺序是您期望的顺序。