Mysql: Conditional select / concat depending on column matches

Question

I have a database with user details in one table and linked contact details in another table (where the contact details are stored as "content").

I am trying to make a quick search function where you can search for the name or any contact details. So you can either search for name or an email or phone (whatever is in the contact detail field).

This is what I have so far:

SELECT DISTINCT leads.id, CONCAT(first_name,' ', last_name) AS name

FROM `leads` 

INNER JOIN `contact_details` ON contact_details`.`lead_id` = `leads`.`id` 

WHERE ((CONCAT(first_name, last_name, content) LIKE ('%[XXX]%')));

This works fine. You can search for f ex "55" and it will return hits on f ex ph number 555-573-3222 or you can search for a name string like 'Joh' and it will match 'Johnson'.

My problem, though, is that regardless of what you are searching for, what is being returned is client name. Since this is for an autocomplete feature, this is obviously very confusing. If you start typing in 555-2 you want to see the suggestion 555-221-6362 not "John Johnson".

How can I return EITHER a phone number or email (from column "content") OR the concact first_name, ' ', last_name depending on whether the search matched a name or a contact_detail.content.

Since I am searching on first_name OR last_name, the search works well for "joh" matching "John" but obviously breaks when you search for "John Stan" for "John Stanley". Is there a Mysql way of fixing this or do I need to clean up string before and do alternative searches if there is a space (searching first_name AND last_name separately)

Any suggestions would be greatly appreciated as I have struggled with this for days now.

Answer 1

Charliez, per our comment conversation, the following is an example of how to do some nested if/thens as well as the break out of the where. You'll need to adjust this to met your specific needs, but should give you enough of an example that you should be able to get things working.

SELECT
  IF(last_name LIKE '%JAM%', 
    last_name, 
    IF(first_name LIKE '%JAM%', 
       first_name, 
       ''
      )
    ) AS MatchedFieldText
FROM employee
WHERE 
  last_name LIKE '%JAM%' 
  OR first_name LIKE '%JAM%';

Mysql: Conditional select / concat depending on column matches

Sign up