Advice? How to Tag?

[eluser]georgerobbo[/eluser]
I am looking to add tags to my posts. I know you should have a different table for tags and posts. However I am unsure how to link the two?

My database structure is as follows.

Code:

--
-- Table structure for table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `ID` int(11) NOT NULL auto_increment,
  `title` varchar(25) NOT NULL,
  `permalink` varchar(50) NOT NULL,
  `author` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `filename` varchar(25) NOT NULL,
  `description` varchar(200) NOT NULL,
  `category` varchar(25) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

[eluser]daparky[/eluser]
In the tag table you would have something like tagid, postid and tagname. Whenever you add a tag you will need to add it to the tag table with the post id. If you use a tagname twice you will need to check to see if the tagname is in already there and use the tagid. Then you would do a tag page and do a query like select * where tagid = 1.

Hope this makes sense.

[eluser]jedd[/eluser]
You need two tables.

tag
id
name

post_tag
id
tag_id // links to tag.id
post_id // links to post.id

[eluser]georgerobbo[/eluser]
Is there a better, cleaner method for selecting the ID from the variable $data['rand']

My controller is the following.

Code:

<?php

class Welcome extends Controller {

    function Welcome()
    {
        parent::Controller();    
    }
    
    function index()
    {
        $data['title'] = "Home";
        
        $this->load->model('Category');
        
        $data['cat'] = $this->Category->list_category();
        $data['rand'] = $this->Post->get_random_single();
        $data['new'] = $this->Post->latest_submissions();

        foreach ($data['rand'] as $item): $postid = $item['ID']; endforeach;
        $data['tag'] = $this->Post->get_tags($postid);

        $this->load->view('meta', $data);
        $this->load->view('header', $data);
        $this->load->view('home', $data);
    }

[eluser]jedd[/eluser]
[quote author="georgerobbo" date="1255995375"]Is there a better, cleaner method for selecting the ID from the variable $data['rand']

[/quote]

I don't understand the question. What is $data['rand'] and what does get_random_single() do?

[eluser]georgerobbo[/eluser]
Sorry. $data['rand'] selects everything from the table below (which contain the posts).

Code:

--
-- Table structure for table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `ID` int(11) NOT NULL auto_increment,
  `title` varchar(25) NOT NULL,
  `permalink` varchar(50) NOT NULL,
  `author` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `filename` varchar(25) NOT NULL,
  `description` varchar(200) NOT NULL,
  `category` varchar(25) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

So to get the tags relevant to those posts I am querying the tag table with the ID of the post being displayed. To do so I need to set the ID of the post in my controller and I have done that using a foreach loop. I was wondering if there was a prettier way of doing it.

[eluser]jedd[/eluser]
Oh, okay.

get_tags ($postid) would then contain some SQL like this (untested, of course, so play with this a bit)

SELECT tag.name FROM post_tag WHERE post_tag.post_id = $postid LEFT JOIN tag ON tag.id = post_tag.tag_id