Better practice to do this?

[eluser]Lockzi[/eluser]
I've been seeing some similar examples to the problem I have, but none has been working or been fully similar.

TOPICS TABLE
| id | subcategory_id | generic_name | name | type | started_by | last_post | last_post_by | replies | views |

USERS TABLE
| user_id | user_login | user_pass | useraccess | user_screenname | usergroup_id | user_posts | user_title | user_email | user_sig | user_joined | user_lastpost | user_lastlogin | user_msn | user_aim | user_timeformat | user_avatar | user_blocked | user_blockedmsg | user_ip | user_website | user_usesignature | user_usesmileys |

Model

Code:

function getTopics()
    {
        $generic_name = $this->uri->segment(1);

        $this->db->select('topics.*, users.*, forum_subcategories.*');
        
       $this->db->join('users',
                        'users.user_id = topics.started_by', 'left');
        $this->db->join('forum_subcategories',
                        'forum_subcategories.subcategory_id = topics.subcategory_id', 'right');
        $this->db->join('forum_categories',
                        'forum_categories.category_id = forum_subcategories.category_id');
        
        $this->db->where('forum_subcategories.subcategory_generic_name', $generic_name);
        
        $query = $this->db->get('topics');
        
        return $query;
    }

This all works out fine, except that I'm not able to do a join again for getting the topics.last_post_by since it overwrites the user.user_screenname from topics.started_by (it can be a different user who started the topic and another user who posted the last thing in the topic). Both started_by and last_post_by are INTS that corresponds to the ID of a user.

So what I then tried to do is to manipulate the data in the controller by doing this:
Controller

Code:

function doForum()
    {
        $this->forum['rawTopics'] = $this->f->getTopics();
        
        foreach ($this->forum['rawTopics']->result() as $topic) {
            $this->forum['topics'][$topic->id]['last_post_by_userName']    = $this->user->idToName($topic->last_post_by);
            $this->forum['topics'][$topic->id]['started_by_userName']    = $this->user->idToName($topic->started_by);
        }
        
        $this->_run("forum");
    }

What it's suppose to do is to make a new variable (I would prefere to edit the initial variable...), and add ['last_post_by_userName'] and ['started_by_userName'] to each row which should contain the username for both last_post_by and started_by.

I'll also show you my view

Code:

<div id="forum-container">

    <div id="forum-name">&lt;?= $rawTopics->row()->subcategory_name ?&gt;</div>

    <div class="forum-title-header">
        <div class="forum-topicpicture-header">
        </div>
        <div class="forum-lastpost-header">
            Last Post
        </div>
        <div class="forum-views-header">
            Views
        </div>
        <div class="forum-replies-header">

            Replies
        </div>
        Topic Title
    </div>
    &lt;?php
    
    foreach ($topics as $row) {
    ?&gt;
    <div class="forum-title">
            <div class="forum-topicpicture-standard">
            </div>
            <div class="forum-lastpost">
                Posted: &lt;?=$row->last_post_by_userName?&gt;<br />
                Author: <a href="viewProfile/darky-2/">USERNAME</a>
        ...........................................................................

What you should notice about this is that I need to do a $rawTopics->row()->subcategory_name in the top to get out the Subcategory (as like a page title) for those topics, which needs to be outside of the foreach loop (since we only need it once.)

I've never looped through the results in my controller trying to edit the outcome before, so I have no idea if it's the right way of doing it. If you can come up with a better solution don't hold back!


EDIT: I should also mention that my example doesn't work as well


Best regards,
Lockzi