Tauras Tauras - 1 month ago 19
MySQL Question

How to correctly use CONCAT in query

This query works perfectly, if i know all three fields. For example: Name1 Name2 Name3, or even first two or last two names.

SELECT
id, forename, middlename, surname
FROM peoples
WHERE
forename LIKE '%$search_keyword%' OR
middlename LIKE '%$search_keyword%' OR
surname LIKE '%$search_keyword%' OR
CONCAT(forename, ' ',middlename, ' ',surname) LIKE '%$search_keyword%' ORDER BY forename DESC LIMIT 50";


However, it is not working, if i am searching for the same result, but without knowing all names.

For example: Name1 Name3, gives me 0 results.

Example: if i type John Lewis, but the full name for this one is John Shop Lewis, it must find this result.

Also, person might not have middlename, so by default it is set to null.

Answer

Why not just add OR CONCAT(forename, ' ',surname) LIKE '%search_keyword%' ?

(That is, if you absolutely want to do it in SQL and not with PHP's explode, as suggested by @aynber in their comments.

| id | forename | middlename | surname |
|----+----------+------------+---------|
| 1  | John     | Shop       | Smith   |
| 2  | John     | NULL       | Smith   |

The following query

SELECT id, forename, middlename, surname 
FROM peoples
WHERE 
    forename LIKE '%John Smith%'
    OR middlename LIKE '%John Smith%'
    OR surname LIKE '%John Smith%' 
    OR CONCAT(forename, ' ',middlename, ' ',surname) LIKE '%John Smith%'
    OR CONCAT(forename, ' ',surname) LIKE '%John Smith%'
ORDER BY forename LIMIT 50

Will return both lines 1 and 2.

Comments