php - MYSQL match keyword list Comma Delimited Text Field -

i have list of keywords in field called 'keywords' in mysql database example keywords field in record contains 3 sample records

keyword list 1: tree,apple,banana,cherry,flower,red apple,pink cherry,cat,mouse  keyword list 2: cat,mouse,apple,red apple,flower,red appleberry  keyword list 3: apple, red appleberry, flower 

the keyword list not array - text field in mysql database has lot of other fields.

when run mysql select query i've seen 'so far' there 2 ways: a) %% b) match against

let's want run query against word 'apple'

select * keywords '%apple%' table

this bring records contain word 'apple' including record above wouldn't give me sequence of higher or lower. i'd have do filtering of results post query.

supposing more detailed in query, , chose 'red apple' still show match, wouldn't have keyword list 1 more relevant 2 or 3.

someone suggested using match against instead

select *, match(keywords) against('apple') score table keywords '%red apple%' order score desc; 

this heading in right direction - won't sort results exact match being found more relevant. relevancy based on how many repetitions of word 'apple' appeared in keyword list (this age old reason why search engines chose ignore keywords altogether) - see i'm going this?

what i'm looking process of logic in mysql instead of reading array , processing in php others have suggested. hence simplistic example.

here's query ought how should be:

1) if query 'apple' keyword list 1 should show first

2) if query 'red apple' keyword list 2 should show first because word 'red apple' closer front of keyword list - closer beginning of string.

but because of %%

keyword list 3 show though match 'red appleberry' instead of 'red apple'

(it simplest if mysql had kind of 'explode' function specify comma (',') delimiter in match don't know of such method, you? of course you'd have read entire list of results array , explode them in php.

3) suppose search 'red apple' , here's bug: i'd still have match on 'red appleberry' (record 3) - don't want that. record 2 should show , record 1 , not show record 3.

this discussion , inquiry. have suggestions?

my own answer: include comma in query instead of searching 'red apple' search 'red apple,' if user put space between - or if @ tail end of list of keywords?

question: how can search specific score in comma delimited text field in mysql uses exact word (not fragment) come ordered list of results.

every attempt on far still have keyword list 3 show higher 2 though red appleberry , red apple 2 different phrases separated commas.

thanks! let's discuss!

for small project, can this

select *,  case when keyword '%red apple%' 1 else 0 end exact_match,     match(keywords) against('apple') score table keywords '%apple%'     order exact_match desc, score desc;