select username from different table where id matches in codeigniter
<p>I need to get the user name from <code>Login</code>table, the comments come from <code>Report_Comments</code>table that has a FK <code>UserID</code>that is the FK of <code>LoginID</code> in the <code>Login</code> table.</p>

<p>So if <code>UserID</code> is 1 it represents <code>LoginID</code>1.</p>

<p>With this set up I know wish to get the <code>Username</code> from <code>Login</code>table and display it in my view (where it says"user name here").</p>

<p>I cant think how to do it correctly, I am think of a query along the lines of, select Username from Login where UserID IS LoginID</p>

<p><strong>Query idea</strong></p>

<pre><code>SELECT `Username`
FROM   `Login` 
JOIN   `Report_Comments` 
WHERE  `LoginID` =  `UserID` 
</code></pre>

<p>Once I have the correct query how would I set this in my code bellow ?</p>

<p><strong>Model</strong> </p>

<pre><code>    function get_comment()
    {
        $query = $this-&gt;db-&gt;get('Report_Comments');
        return $query-&gt;result();
    }
</code></pre>

<p><strong>View</strong></p>

<pre><code> &lt;h1&gt;comments&lt;/h1&gt;
    &lt;table style="width:100%"&gt;
        &lt;tr&gt;
            &lt;th&gt;&lt;h3&gt;Comment&lt;/h3&gt;&lt;/th&gt;
            &lt;th&gt;&lt;h3&gt;Date&lt;/h3&gt;&lt;/th&gt;
            &lt;th&gt;&lt;h3&gt;User Name&lt;/h3&gt;&lt;/th&gt;
        &lt;/tr&gt;
        &lt;?php if (isset($reports)) :
        foreach ($reports as $row) : ?&gt;
        &lt;tr&gt;
            &lt;td&gt;&lt;?php echo $row-&gt;Comments; ?&gt;&lt;/td&gt;
        &lt;/tr&gt;
        &lt;tr&gt;
            &lt;td&gt;&lt;?php echo $row-&gt;Comment_Date; ?&gt;&lt;/td&gt;
        &lt;/tr&gt;
        &lt;tr&gt;
            &lt;td&gt;username here&lt;/td&gt;
        &lt;/tr&gt;
    &lt;/table&gt;
    &lt;hr&gt;
    &lt;?php endforeach; ?&gt;

    &lt;?php else : ?&gt;
        &lt;p&gt;No Comments&lt;/p&gt;
    &lt;?php endif; ?&gt;
</code></pre>

<p><strong>Controller</strong></p>

<pre><code>function comments()
    {
        $data = array();

        $this-&gt;db-&gt;where('ReportID', $this-&gt;uri-&gt;segment(3));

        if ($query = $this-&gt;report_model-&gt;get_comment()) {
            $data['reports'] = $query;
        }

        $this-&gt;template['middle'] = $this-&gt;load-&gt;view($this-&gt;middle = 'comments/comment_view', $data, true);
    }
</code></pre>