mySQL - Find and group similar values with wildcard

Question

Hy guys,

i searched some questions on stackoverflow, but i found no solution for my problem :(

I have a table with some values of a internal search database:

ID    searchword     hits
1     php             200
2     html             88
3     php state        68
4     php statement    43
5     js               83
6     html img         48
7     html include     34

And so on... I need a query to group searchwords, but also the similars. For example I need this result for the table above:

 searchword
  php statement      (because 'php' and 'php statement' is similar)
  js                 (because it´s only one row and standalone)
  html img           (because 'html' is similar, it´s included)
  html include       (because 'html' is similar, it´s included)

Is that possible? The query should not be static, because I don´t know all searchwords in the table. I think I need a DISTINCT and a LIKE query with wildcard (%)?!

But I don´t get it :(

http://sqlfiddle.com/#!9/cd24c/28

Regards Ronny

Its only a example. I build a fiddle for better understanding — RonnyDee, Nov 13 '17 at 13:17
FWIW, this kind of task rapidly becomes horrendously difficult in SQL. — Strawberry, Nov 13 '17 at 13:22
So the GROUP BY should group (php, php state, php statement), (html, html img, html include), (js)... if i understand your question correctly... how should the excepted results look like? — Raymond Nijland, Nov 13 '17 at 13:23
Unless you have the search keywords in somewhere else you **CAN'T** do this query. Also you need priorities for those keywords. What if a row is `PHP HTML` what group will you assign it? — Juan Carlos Oropeza, Nov 13 '17 at 13:29
@JuanCarlosOropeza is right. It can be done if you want to assign group based on first word only. — Harshil Doshi, Nov 13 '17 at 13:36
@Harshil I thought about that but in the sql fidlle he have data like `46` and `4634` plus `inter` and `interface` — Juan Carlos Oropeza, Nov 13 '17 at 13:37
Hmm... i think about a subquery like ' WHERE searchword LIKE "% %" ' - and it should only group similar words. For example if i have "html", "php" and "html php" - it´s a result of 3 standalone hits. — RonnyDee, Nov 13 '17 at 13:38
You can use text search ... something like this https://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity — Juan Carlos Oropeza, Nov 13 '17 at 13:40

mySQL - Find and group similar values with wildcard

0 Answers0