如何使用MySQL和PHP在数据库中存储标签? [英] How To Store Tags In A Database Using MySQL and 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.

这篇关于如何使用MySQL和PHP在数据库中存储标签?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！