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]%')));
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%';