在PHP / MySQL中使用标签进行搜索

Question

I am struggling to find a solution to this. I have tags that are inserted into the database in this form

mytags=firsttag, secondtag, thirdtag

On the single page I need to show related items based on these tags aso I am using

SELECT * FROM posts WHERE tags LIKE \"%$mytags%\" ORDER BY views DESC LIMIT 5"

However this doesn't work much as it only searches for posts that have the exact same tagline (firsttag, secondtag, thirdtag) and not the posts that their tag contain one of the tags (firsttag, anothertag, andanothertag).

I thought of exploding mytags and then search using the first tag in the array, but this doesn't work very good.

Does anyone know an effective method of searching?

Answer 1

Why don't you use separate table for post tags keeping? I mean for example table 'posts', table 'tags' and relation table 'posts_tags'. Here is structure of 'posts':

id
title
content

Here is structure of 'tags':

id
tag_name

And here is structure if 'posts_tags':

id
post_id
tag_id

This is scheme of 'many to many' relationship model. And you can now simply find tags or/and posts through JOIN operator.