CODEWITHSUNDEEP
phpmysqlcsvmatchmatch-against
Viktor
Viktor

Reputation: 547

MYSQL match keyword list Comma Delimited Text Field

I have a list of keywords in a field called 'keywords' in a MYSQL database For example the keywords field in the 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 is NOT an array - just a text field in a mysql database that has a lot of other fields.

When I run a MYSQL SELECT query from what I've seen 'so far' there are two ways: a) like %% b) match against

Let's say I want to run a query against the word 'apple'

SELECT * where keywords like '%apple%' from table

This would bring up the records that contain the word 'apple' including the record above but it wouldn't necessarily give me any sequence of higher or lower. I'd have to do do the filtering of the results post query.

Supposing I was more detailed in my query, and chose 'red apple' it would still show a match, but I wouldn't necessarily get have KEYWORD LIST 1 be more relevant than 2 or 3.

Someone suggested using the Match Against instead

SELECT *, MATCH(Keywords) AGAINST('apple') AS Score
FROM table
WHERE Keywords like '%red apple%'
ORDER BY score DESC;

This is certainly heading in the right direction - however will it won't sort the results by an exact match being found as more relevant. The relevancy would be based on how many repetitions of the word 'apple' appeared in the keyword list (this is the age old reason why all the search engines chose to ignore keywords altogether) - do you see where I'm going with this?

What I'm looking for is do process most of the logic in the MYSQL instead of reading it into an array and processing it in PHP as others have suggested. Hence this simplistic example.

Here's what the query ought to how it should be:

1) if my query is 'apple' Keyword list 1 should show up first

2) if my query is 'red apple' then Keyword list 2 should show up First because the word 'red apple' is CLOSER to the FRONT of the keyword list - closer to the beginning of the string.

BUT because of the LIKE %%

Keyword List 3 would show up even though the match was 'red appleberry' instead of 'red apple'

(It would be simplest if MySQL had some kind of 'explode' function that you could specify the comma (',') as the delimiter in the match but I don't know of any such method, do you? Of course you'd have to read the entire list of results into an array and then explode them in PHP.

3) Suppose I search for 'red apple' and here's the bug: I'd still have a match on 'red appleberry' (record 3) - I don't want that. Record 2 should show up and then Record 1 and not even show record 3.

This is a discussion and an inquiry. Anyone have any suggestions?

My OWN ANSWER: include the comma in the query instead of searching for 'red apple' search for 'red apple,' but what if the user put a space between - or if its at the tail end of the list of keywords?

QUESTION: How can we search for specific SCORE in a comma delimited text field in MYSQL that uses the exact word (not a fragment) to come up with an ORDERED list of results.

Every attempt on this so far will still have Keyword List 3 show up higher than 2 even though red appleberry and red apple are two different phrases separated by commas.

Thanks! Let's discuss!

Upvotes: 0

Views: 1429

Answers (1)

Tim3880
Tim3880

Reputation: 2583

For small project, you can do something like this

SELECT *, 
case when keyword like '%red apple%' then 1 else 0 end exact_match,    
MATCH(Keywords) AGAINST('apple') AS Score
FROM table
where keywords like '%apple%'    
ORDER BY exact_match DESC, score DESC;

Upvotes: 1

Related Questions