Search code examples
phpmysqltagging

Tagging text using tags stored in database

in my new PHP project, I'm going to get a large amount of text as input.

And I have hundreds of tags stored in my mySQL database:

|  id   |       tag        |
+-------+------------------+
|   1   |       Halo       |
|   2   |       LIMBO      |
|   3   |    Super Mario   | 
|   3   | Metal Gear Solid |  <-- multiple words as single tag

The inputs are also stored in mySQL, before being processed.

How can I check each input for these tags? using explode(' ' ? strpos() ? LIKE '%TAG1%' OR LIKE '%TAG2%' ? ... how can I gain maximum performance with PHP and mySQL?

Thanks

P.S.

How about an input in HTML format?

Solution

  • For maximum coincidence set FULLTEXT-index to field tag. Then in query use next design :

        SELECT * FROM `tags`
    WHERE MATCH(tag) AGAINST (YOUR_INPUT_VALUE)

    Read Full-Text Search Functions