How To Store Tags In A Database Using MySQL and PHP?

Question

I wanted to create a database that will store the tags that users enter for there 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 know.

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.

Answer 1

Tables:

CREATE TABLE questions_tags (
tag_id INT INT NOT NULL,
question_id INT NOT NULL,
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

---

SQL

Select the tag related to a question

SELECT     tag_id
FROM         question_tags
WHERE     question_id = $yourquestionid'

Select the questions related to a tag

SELECT     question_id
FROM         question_tags
WHERE     tag_id = $yourtagid'

Count how many times a tag has been used

SELECT COUNT(tag_id) 
FROM question_tags 
where tag_id=$yourtagid

Make a tag cloud

SELECT COUNT(tag_id)
FROM question_tags 
GROUP BY tag;

Answer 2

You would probably need a n:m relation table that stores the relations to your "question"-Table.

Answer 3

You should split your data betwwen 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
);

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.

Edit

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

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

Edit

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;

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

Note: All SQL above is untested.