CODEWITHSUNDEEP
phpmysqlconcatenation
Tauras
Tauras

Reputation: 3904

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.

Upvotes: 0

Views: 63

Answers (2)

roberto06
roberto06

Reputation: 3864

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.

Upvotes: 1

aynber
aynber

Reputation: 23011

Since there are many permutations of names, it's easier to break it apart and search for each particular section, like this:

$names = explode(' ',$search_keyword);
$concat_arr = array();
foreach($names as $name) {
    $concat_arr = "CONCAT(forename, ' ',middlename, ' ',surname) LIKE '%$name%'";
}
$query = "SELECT 
  id, forename, middlename, surname 
FROM peoples 
WHERE ";
$query .= implode(' AND ',$concat_arr);
$query .= " ORDER BY forename DESC LIMIT 50";

Upvotes: 3

Related Questions