php - MySQL错误: Unknown column 'product_name' in 'where clause' in opencart

Question

我尝试使用以下 MySQL 查询从 order_product 表中检索产品名称:

查询

SELECT o.order_id, 
       CONCAT(o.firstname, ' ', o.lastname) AS customer, 
       ( SELECT os.name 
         FROM order_status os 
         WHERE os.order_status_id = o.order_status_id 
         AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' 
       ) AS order_status, 
       ( SELECT name 
         FROM order_product 
         WHERE order_id = o.order_id 
         LIMIT 1 
       ) AS product_name, 
       o.shipping_code, 
       o.total, 
       o.currency_code, 
       o.currency_value, 
       o.date_added, 
       o.date_modified, 
       o.telephone, 
       o.shipping_city, 
       o.order_type, 
       o.shipping_address_1 
FROM   order o 
AND    product_name LIKE '%smart tv3%'`

请帮我解决这个错误。并给我一个指导方针。

This is the error, I got it.

Answer 1

我通过将查询中的 AND 替换为 HAVING 解决了该错误。 :)

查询

SELECT
   o.order_id,
   CONCAT(o.firstname, ' ', o.lastname) AS customer,
  (
  SELECT
     os.name 
  FROM
     order_status os 
  WHERE
     os.order_status_id = o.order_status_id 
     AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' 
)
 AS order_status,
(
  SELECT
     name 
  FROM
     order_product 
  WHERE
     order_id = o.order_id LIMIT 1 
)
 AS product_name,
 o.shipping_code,
 o.total,
 o.currency_code,
 o.currency_value,
 o.date_added,
 o.date_modified,
 o.telephone,
 o.shipping_city,
 o.order_type,
 o.shipping_address_1 
FROM
 order o 
HAVING
 product_name LIKE '%smart tv3%'`

感谢@wayneOS 和其他人回答我的问题:)