Check if any part of string1 matches any part of string2

Question

I have simple search function that uses

WHERE data LIKE '%keyWord%';

but what if I use many key words

"many key words"

and my data contains

"some key data"

I want them to match up even if one word matches ("key" in this case). Can this be achieved in one MySQL query?

use the `OR` operator or explode *(or is that implode?, I get mixed up)* on the data searched — Funk Forty Niner, Dec 29 '16 at 22:11
The problem is I can't use many ORs because keyword even if it contains many words has to be a single string. — Vortic, Dec 29 '16 at 22:13
https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html — shmosel, Dec 29 '16 at 22:16
See http://stackoverflow.com/q/19327108/1415724 --- http://stackoverflow.com/q/30029537/1415724 --- http://stackoverflow.com/q/14055116/1415724 - one of those stand to be a possible duplicate. — Funk Forty Niner, Dec 29 '16 at 22:24
I think I'll fiddle with PHP's explode() to change the string to single words. — Vortic, Dec 29 '16 at 22:32
Possible duplicate of [SQL table with "list" entry vs SQL table with a row for each entry](http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry) — e4c5, Dec 30 '16 at 08:52

Vortic · Accepted Answer · 2017-01-25T10:29:52.140

0

Ok, so here's how I dealt with it.

            $keyWords = $_POST["searchedWords"];
            $exploded = explode(" ",$keyWords);
            $allTags = '';
            foreach ($exploded as $tag) {
                $allTags .= " or data like '%".$tag."%'";
            }

            $query = "select something, something2 from myTable
            where data = 'ReallyHardTagThatNoOneWillGuess'$allTags";

This gives a query ready to search table with every word in a string not just string as a whole.

edited Jan 25 '17 at 10:29

answered Dec 30 '16 at 00:16

Vortic

75
1
8

Just a little correction. It should be $allTags .= " or data like '%".$tag."%'"; – Vortic Dec 30 '16 at 10:43

score 0 · Answer 2 · answered Dec 30 '16 at 08:35

Unless your table contains a few rows LIKE "%text%" should be avoided, since no index can be used.

Instead you should consider to add a full text index on column data and perform a full text search, e.g.

ALTER TABLE mytable ADD FULLTEXT(data);
SELECT col1, col2, ... FROM mytable WHERE match(data) AGAINST("word1 word2 word3" in BOOLEAN MODE);

Check if any part of string1 matches any part of string2

2 Answers2