Unknown Column In Where Clause (as)

Question

SET @value = '';
SELECT 
    id, order_kind, property_kind, town, images, agreement, uprice, eprice,
    tprice, pprice, bprice, qprice, status, createtime, town_title, fullname, 
    mobile, profile_image, area, barea, address, 
    CONCAT(id+9000) AS sid,
    CONCAT(area,' m') AS marea,
    CONCAT(barea,' m') AS mbarea 
FROM `sells` 
WHERE `order_kind` = '$order_kind' 
  AND `property_kind` = '$property_kind' 
  AND (
      sid LIKE CONCAT('%', @value, '%') || 
      town_title LIKE CONCAT('%', @value, '%') || 
      fullname LIKE CONCAT('%', @value, '%') || 
      mobile LIKE CONCAT('%', @value, '%') || 
      marea LIKE CONCAT('%', @value, '%') || 
      mbarea LIKE CONCAT('%', @value, '%') || 
      address LIKE CONCAT('%', @value, '%') 
  )

I use this sql for searching, and erroring unknown column 'sid' in where clause
i change where to having but not work php loop

Duplicate of https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error — Qirel, Nov 12 '20 at 21:44

score 1 · Answer 1 · answered Nov 12 '20 at 21:36

Your query defines sid as CONCAT(id+9000) AS sid in the SELECT clause. You can't reuse that alias in the WHERE clause. Instead, you need to repeat the expression.

So basically change this condition:

sid LIKE CONCAT('%', @value, '%')

To:

CONCAT(id+9000) LIKE CONCAT('%', @value, '%')

Important note: use prepared statements! Do not concatenate PHP variables in the query string: this is both inefficient and unsafe. Recommended reading: How can I prevent SQL injection in PHP?

Unknown Column In Where Clause (as)

1 Answers1