CODEWITHSUNDEEP
phpmysql
php
php

Reputation: 41

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?

Upvotes: 4

Views: 5214

Answers (3)

lutz
lutz

Reputation:

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.

Upvotes: 7

Roch
Roch

Reputation: 22061

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;

Upvotes: 2

Zeemee
Zeemee

Reputation: 10714

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

Upvotes: 0

Related Questions