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

mysql, php

Solution

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
);

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.

Problem

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?

Original source