Beep Beep - 7 months ago 22
SQL Question

select username from different table where id matches in codeigniter

I need to get the user name from

Login
table, the comments come from
Report_Comments
table that has a FK
UserID
that is the FK of
LoginID
in the
Login
table.

So if
UserID
is 1 it represents
LoginID
1.

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

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

Query idea

SELECT `Username`
FROM `Login`
JOIN `Report_Comments`
WHERE `LoginID` = `UserID`


Once I have the correct query how would I set this in my code bellow ?

Model

function get_comment()
{
$query = $this->db->get('Report_Comments');
return $query->result();
}


View

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

<?php else : ?>
<p>No Comments</p>
<?php endif; ?>


Controller

function comments()
{
$data = array();

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

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

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

Answer

Put this in your model:

$this->db->select('Report_Comments.Comment, Report_Comments.Date, Login.Username')
  ->from('Report_Comments')
  ->join('Login', 'Report_Comments.UserID = Login.LoginID');
$result = $this->db->get();

Then result will have the comment, the date and the username as well.

Comments