MySQL FULLTEXT index trouble. There are:
- Table with words like:
| ID | WORDS |
|---|---|
| 1 | jfjdeur,rjjghfje,rioogr |
| 2 | fkjtifdfe,lerkr |
| 3 | kfrkriti |
| 4 | frlerkti,tykitriero,frorodfl,rfjkrjr |
| ... | |
| N | fjfjtiu,frkrker,fkdkri |
MySQL FULLTEXT index on WORDS column.
Query like:
SELECT *
FROM `table`
WHERE match(`words`) against ('gjfjdeur,rioogr,tykitriero')
As the result returns:
| ID | WORDS | |
|---|---|---|
| 1 | gjfjdeur,rjjghfje,rioogr |
(because contains gjfjdeur and rioogr) |
| 4 | frlerkti,tykitriero,frorodfl,rfjkrjr |
(because contains tykitriero) |
Is it possible to rewrite query to create additional column which will be contain found/matched word? Something like:
| ID | WORDS | FOUND |
|---|---|---|
| 1 | gjfjdeur,rjjghfje,rioogr |
gjfjdeur,rioogr |
| 4 | frlerkti,tykitriero,frorodfl,rfjkrjr |
tykitriero |
Maybe something like:
SELECT *, some_function_to_select_matched_range(`word`) as found
FROM `table`
WHERE match(`words`) against ('gjfjdeur,rioogr,tykitriero')
In realily, table.words consists of hundreds words and preg_match_all in php isn't a good solution to select the matched words from each found.