0

I have a list of products

CREATE TABLE products (
id int,
description varchar(1000)
);

I want to search the description with this term cool black shirt. If any of these words appear in the description, I want it to show in the results. I also want it to be sorted by the how well it was matched from the search term. I am unsure how to do this.

The first challenge I am having is how can I split the term with spaces to create the LIKE bit. I am only given the string cool black shirt. I can't add each word manually. Needs to be automatic.

The second challenge I face is how do I sort the results by how well it matched the original term.

SELECT * from products
WHERE description LIKE 'cool black shirt'
ORDER BY ...;
Shadow
  • 33,525
  • 10
  • 51
  • 64
Paul
  • 127
  • 1
  • 1
  • 7
  • Mysql alone is not necessarily suitable for tis task. Fulltext search can kind of do what you are asking for, but it only works based on occurances, not meaning. – Shadow Nov 09 '20 at 07:33
  • @Shadow How do I do with meaning? If it can't be done via MySQL, how does the SQL statement look regardless? (I can form the statement using `PHP`). Because I get how to do the LIKE but. I just have to do `LIKE 'cool' OR LIKE 'black'` etc. But how do I sort it? – Paul Nov 09 '20 at 07:38
  • If you want to search based on meaning, you need to get familiar with natural language processing (NLP). Probably Python and R hava the most advanced libraries for this purpuse, such as NLTK, spacy, or BERT. The answers to the duplicate topic show you how to the mysql part with fulltext search. If you are interested how fulltext search ranking works, look up TF-IDF algorithm. – Shadow Nov 09 '20 at 07:43

0 Answers0