mysql search, returning the column names where the search was successful

Question

I have a very simple search query that looks like this

select * from products_stock where (
    title like '%$searchterm%' or 
    short_desc like '%$searchterm%' or 
    long_desc like '%searchterm%'
)

When I get a hit from the column long_desc, I want to program in some special behaviour in PHP. Is there any way to get the mysql results to highlight which column the search was successful in?

Answer 1

You can use derived fields. Looks ugly, but works:

SELECT *, (title LIKE '%$searchterm%') AS found_in_title,
     (short_desc LIKE '%$searchterm%') AS found_in_short_desc
     etc...

the LIKE will return a boolean true/false as a field in your result set, and the AS alias will tell you which field the match occured in.

Note that you'd probably be much better off using a fulltext index for these sorts of things. LIKE '%...%' matches cannot use indexes, and performance will be utterly abysmal on "large" tables.