Using concat?

PHP
1

I’m trying to combine to columns into one in a query, but am doing something wrong?

$sql = "SELECT concat(fname,' ',lname) AS name FROM ".$prefix."_users WHERE name LIKE '%" . $queryString . "%' ORDER BY name LIMIT 8";

I get this error:
Error: (1054) Unknown column ‘name’ in ‘where clause’

Can somebody tell me what I’m doing wrong?!

Thanks in advance…

2

From memory I’m pretty sure and someone can correct me if I’m wrong but isn’t AS just an array identifier for the output?

I’m, pretty that within the query column names have to match to the T with the correct case.

3

The manual states you can sort by an alias, try wrapping name backticks? name

4

Yep, confirmed. :slight_smile:

5

unfortunately, while you can use a column alias in the GROUP BY, HAVING, and ORDER BY clauses, you cannot use it in the WHERE clause

there are two solutions – repeat the expression in the WHERE clause, or “push down” the alias into a subquery (i.e. derived table a.k.a. inline view)


SELECT CONCAT(fname,' ',lname) AS name 
  FROM daTable 
 WHERE CONCAT(fname,' ',lname) LIKE '%foo%' 
ORDER 
    BY name LIMIT 8


SELECT name 
  FROM ( SELECT CONCAT(fname,' ',lname) AS name 
           FROM daTable ) AS t
 WHERE name LIKE '%foo%' 
ORDER 
    BY name LIMIT 8
6

Perfect… Works like a charm, actually… I got the same as your first code, but thanks anyway :wink: