Using 'OR' between HAVING and WHERE clause in MySQL?

Question

I am trying to fetch records in MySQL using a simple used submitted field. More precisely, the user inputs a name (firstname or lastname or fullname) and the server should return matched rows.

What I am doing so far is something like:

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

That works well for now, but that (obviously) won't work when a user submits the fullname. Is there a way to add a OR between the whole 'WHERE type conditions' and the 'HAVING type conditions'? This way I could do something like:

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%' OR 
   HAVING fullname LIKE '%user_submitted_data%'

I know I could just split the original string but that has some negative impact since you have to deal with names containing spaces such as 'De Gaule' and stuff like that.

Answer 1

Just put all conditions into the HAVING clause.

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
HAVING firstname LIKE '%user_submitted_data%'
OR      lastname LIKE '%user_submitted_data%'
OR      fullname LIKE '%user_submitted_data%

The WHERE clause could discard rows early, but since you cannot discard them until after you have evaluated the condition on the computed column, and that has to wait until HAVING, it buys you nothing to use WHERE.

Answer 2

Do a subquery:

SELECT [some fields]
FROM
  SELECT firstname, lastname, CONCAT(firstname, ' ', lastname) as fullname
  FROM people) AS tmp
WHERE firstname LIKE '%user_submitted_data%'
OR lastname LIKE '%user_submitted_data%'
OR fullname LIKE '%user_submitted_data%'