Search engine by tags

Question

So, I'm new in PHP and Database and I have a problem here:

I have a table called news and one of its column is tags. This column contains 1 or more tags separated by whitespaces, like economics politics sports. And I have a page which will receive one or more tags as a single parameter and I have to return all rows that contains at least one of this tags.

I could explode the tags and do as many database access as necessary but I'm sure this is not the way to do it =p

LIKE and IN won't work either because the paramater may contain more than 1 tag in any order.

Any help?

Answer 1

Easiest solution

Convert your table to a MyISAM table and index the tags using a FULLTEXT index. From there, use the MATCH() function to extract a match value for the row against a query string (which may contain multiple words)

More adaptative solution

Drop that table and recreate it with one tag per row. You'll effectively implement a FULLTEXT array this way, as that is exactly what it is. Avoid storing data in a long string if you're planning on exploding it - usually, storing atomic portions is usually a lot more efficient as you won't have to rely on the hungry LIKE operator.

Answer 2

That isn't how to structure that.

Have a table called news, one called tags, and one called tagsToNews.

In tagsToNews just record the news items ID and the tag ID, then do a join to get what you want. This also means a tag can be renamed without breaking everything!