如何用PHP和MySQL的逗号分隔标记系统获取相关帖子？ [关闭]

Question

I'm creating a website using PHP and MySQL to post related sites. I'm using a tagging system for this site with comma separated tags, for example my tags will look like: php, mysql, code, programming.

The reason I use this comma separated tagging system is because I want the user to be able to insert any kind of tag he wants for his post.

I have searched from this site and internet, but the answer is not really relevant to my question. Many tagging system use three table like item, tag_map, and tag, and I think it doesn't work for comma separated tagging system.

So far, I have already create a table with name and tag fields in it. The best solution I have found is using Tf–idf, term frequency–inverse document frequency to weight the post relative to its content.

Are there any other good solutions for this? If there are not I'm really grateful if someone can explain this tf-idf example using php code.

Answer 1

This isn't the way it shouldn't be done, like the comments adressed. You should split up this in three tables:

A table which stores the posts itself;

Table posts
-----------
id
text

A table for all the tags;

Table tags
-----------
id
name

And finally a table to connect the two;

Table postTags
-----------
id
postId
tagId

When selecting data from these, you can use a query like the one below:

SELECT * FROM posts p
INNER JOIN postTags pt ON pt.postId = p.id
INNER JOIN tags t ON pt.tagId = t.id