Database structure for tags system

I'm making a simple PHP Forum, whereby tags are created alongside the main topic.

The table looks like this:

  CREATE TABLE IF NOT EXISTS `topic` (
  `topic_id ` int(100) NOT NULL AUTO_INCREMENT,
  `topic_head` varchar(5) NOT NULL,
  `topic_body` varchar(20) NOT NULL,
  `topic_tag` varchar(20) NOT NULL,
  `topic_date` varchar(20) NOT NULL,
  `topic_owner` varchar(20) NOT NULL,
  PRIMARY KEY (`topic_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Specifically for the tags, I'll be performing a select query in the form of:

$tags = mysql_query("SELECT DISTINCT topic_tags
                       FROM forum_topics")
                       while($tags = mysql_fetch_assoc($tags){   
                       $split_tags  = "$tags";
                       $pieces = explode(",", $split_tags);

Currently, topic_tags are in the format tag1,tag2,tag3,tag4 How can I have it in such a way that each topic tag will be associated with each topic?

Answers


If I've understood correctly, what you want is another table for tags and then a third table to store the relationships. So:

CREATE TABLE `tags` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `t_text` varchar(150) NOT NULL,
  `t_datetime` datetime NOT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `tag_pairs` (
  `tp_id` int(11) NOT NULL AUTO_INCREMENT,
  `tp_topic_id` int(11) NOT NULL,
  `tp_tag_id` int(11) NOT NULL,
  `tp_datetime` datetime NOT NULL,
  PRIMARY KEY (`tp_id`),
  FOREIGN KEY (`tp_topic_id`) REFERENCES topic('topic_id'), 
  FOREIGN KEY (`tp_tag_id`) REFERENCES tags('t_id') 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Then, to get tags for a topic based on its ID ($THIS_ID):

$query = mysql_query("
  SELECT tags.t_text 
  FROM tags, tag_pairs, topic 
  WHERE topic.topic_id = '$THIS_ID'
  AND tag_pairs.tp_topic_id = topic.topic_id 
  AND tag_pairs.tp_tag_id = tags.t_id 
 ");

Need Your Help

Form does not submit when clicked due to modernizr being loaded

javascript html forms modernizr zurb-foundation

I have a webpage which allows you to change the values of users on it. For some reason, probably obvious but I am overlooking, I am not able to click the submit button (and have it do anything). He...

How to correctly set timestamp in logstach from iis log

iis elasticsearch logstash kibana

I am trying to parse iis log files using logstash and send them to elasticsearch.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.