I have a query that searches across multiple tables and fields for whatever someone has searched for.
SELECT SQL_CALC_FOUND_ROWS
user.username,
user.fname,
user.lname,
user.biopic,
work.company,
work.position,
categories.title AS category_title
FROM user
LEFT JOIN work
ON work.user_id = user.id
LEFT JOIN user_categories
ON user_categories.user_id = user.id
LEFT JOIN categories
ON categories.id = user_categories.category_id
WHERE fname LIKE "%er%" OR
lname LIKE "%er%" OR
username LIKE "%er%" OR
company LIKE "%er%" OR
position LIKE "%er%" OR
categories.title LIKE "%er%"
GROUP BY
username
ORDER BY
CASE WHEN fname LIKE "%er%"
THEN 0 ELSE 0 END
+ CASE WHEN lname LIKE "%er%"
THEN 1 ELSE 0 END
+ CASE WHEN username LIKE "%er%"
THEN 2 ELSE 0 END
+ CASE WHEN category_title LIKE "%er%"
THEN 3 ELSE 0 END
+ CASE WHEN company LIKE "%er%"
THEN 4 ELSE 0 END
+ CASE WHEN position LIKE "%er%"
THEN 5 ELSE 0 END
ASC,
lname ASC,
fname ASC,
category_title ASC,
position ASC,
company ASC
LIMIT 10
OFFSET 0;
Is there any efficient way to find exactly what has matched? Fname, lname, company, etc??
