如何使用MySQL和PHP在数据库中存储标签?

本文介绍了如何使用MySQL和PHP在数据库中存储标签?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想创建一个数据库，该数据库将存储用户为他们的问题输入的标签，然后针对每个张贴的问题显示所有标签；像这样的事情.

I wanted to create a database that will store the tags that users enter for their questions and then display them all for each individual question posted; something like here on SO.

这是现在可以为我做所有事情的桌子:

Here is the table that does everything for me now:

CREATE TABLE questions_tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url TEXT NOT NULL, tag VARCHAR(255) NOT NULL, count INT NOT NULL, PRIMARY KEY (id) );

我知道这是不正确的.我还需要其他哪些表?如果需要，还需要更改为该表吗?

I know this is not correct. What other table or tables do I need and what do I need to change to this table if needed?

推荐答案

您应该在两个表questions和tags之间拆分数据，并使用questions_tags联接表将它们关联起来.

You should split your data between two tables, questions and tags and relate them using a questions_tags join table.

CREATE TABLE questions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url TEXT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, tag VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE questions_tags ( question_id INT UNSIGNED NOT NULL REFERENCES questions, tag_id INT UNSIGNED NOT NULL REFERENCES tags );

我不确定您原始表中的count列是做什么用的，所以我跳过了它.

I'm not sure what the count column in your original table is for so I skipped it.

使用上面的表，您可以使用联接查找带有特定标签的所有问题或问题的所有标签.

Using the above tables you can use joins to find all questions with a certain tag or all tags of a question.

修改

要获取每个标签的数量，您可以执行以下操作:

To get the count for each tag you could to something like this:

SELECT tag, count(*) AS c FROM tags GROUP BY tag;

修改

要获取所有问题的所有标签的计数，请执行以下操作:

To get the counts of all tags for all questions do this:

SELECT t.tag, q.question_id, count(*) AS c FROM tags AS t, questions_tags AS qt questions AS q WHERE t.id = qt.tag_id AND qt.question_id = q.id GROUP BY t.id, q.id;

如果只希望特定标签或问题的计数，请添加其他WHERE子句.

If you want only the count for specific tags or questions add additional WHERE clauses.

注意:上面的所有SQL都未经测试.

Note: All SQL above is untested.