MySQL Problem - Help (Unresolved)

[eluser]georgerobbo[/eluser]
Hello,

I am trying to connect blog posts with their related tags. My database structure is as follows.

table: tag

ID Int // This is the Primary Key
post Int // This is the ID of each blog post
tag Varchar // This is the tag

table: post

ID Int // Primary Key
Title Varchar
Author Varchar
Timestamp Varchar
Filename Varchar
Description Varchar
Category Varchar
Comments Varchar
Favourites Varchar
Views Varchar


My problem is that when I join the tables, only the first tag for each post is selected, rather than all of them.

My model, controller and view are:

Code:

function the_post($permalink)
    {
        $this->db->select('*');
        $this->db->from('post');
        $this->db->join('tag', 'post.ID = tag.post');
        $this->db->where('post.ID', $permalink);
        
        $query = $this->db->get();
        return $query->result_array();
    }

Code:

function single()
    {
        $permalink = $this->uri->segment(2);
        $data['current'] = $this->Post->the_post($permalink);
    
        foreach ($data['current'] as $a):
        $id = $a['ID'];
        $count = $a['views'];
        $data['title'] = $a['title'];
        endforeach;
        
        if(isset($id)) {
        
        $data['count'] = $this->Post->count_view($id, $count);
        
        }
        
        $this->load->view('meta', $data);
        $this->load->view('header', $data);
        $this->load->view('single', $data);
    }

Code:

<div id="page">
        &lt;?php foreach ($current as $a): ?&gt;
        <div id="preview" class="extended">
        
            <div id="preview_item" class="extended">
                <img src="&lt;?php echo base_url() . " align="center" alt="" />
            </div>
                      
        </div>
        
        <div id="meta" class="extended">
            <h2><a href="&lt;?php echo site_url(); ?&gt;/id/&lt;?php echo $a['ID']; ?&gt;">&lt;?php echo $a['title']; ?&gt;</a></h2>
            <h5><small>Author:</small> <a href="&lt;?php echo site_url(); ?&gt;/profile/&lt;?php echo $a['username']; ?&gt;">&lt;?php echo $a['author']; ?&gt;</a></h5>
            <h5><small>Uploaded:</small> &lt;?php $timestamp = strtotime($a['timestamp']); $timestamp = date('F j, Y', $timestamp); echo $timestamp; ?&gt;</h5>
            <h5><small>Tags:</small>&lt;?php echo $a['tag']; ?&gt;</h5>
            <h5><small>Category:</small>&lt;?php if(isset($a['category'])) { echo $a['category']; } else { echo "None"; } ?&gt;</h5>
            <p class="extended"><small>&lt;?php echo $a['description']; ?&gt;</small></p>
            <p class="ref"><small>&lt;?php echo $a['comments']; ?&gt; comments</small></p>
            <p class="ref"><small>&lt;?php echo $a['favourites']; ?&gt; favourites</small></p>
            <p class="ref"><small>&lt;?php echo $a['views'] + 1;?&gt; views</small></p>
        </div>
        &lt;?php endforeach; ?&gt;
    </div>

[eluser]markup2go[/eluser]
Take a look at the MySQL GROUP_CONCAT() function, I believe that is what you're looking for.

[eluser]georgerobbo[/eluser]
Thanks. I'm afraid I don't quite understand how to use it with my join and select statement?

[eluser]markup2go[/eluser]
You would use it in your select. Here's an example of how I did it with a similar database setup:

Code:

$this->db->select("classes.*, GROUP_CONCAT(DISTINCT DATE_FORMAT(class_dates.date, '%m/%d/%Y') ORDER BY date ASC SEPARATOR ',') as dates", FALSE);

So you would do something like

Code:

$this->db->select("post.*, GROUP_CONCAT(DISTINCT tag.tag ORDER BY tag ASC SEPARATOR ',') as tag", FALSE);