how to join these tables ?

[eluser]amrnt[/eluser]
comments:
id : int
from : int
to : int
comment : text

users:
id : int
name : varchar

i need everything controller page , view and model "i wanna use $this->db in models"

e.g.
users
id : name
---------
1 : John
2 : Dan


i want this
John commented for Dan.
instead of
1 commented for 2.

hope you got it, thanks in advance

[eluser]stuffradio[/eluser]
You need to post the code that you have before we/I can help you...

[eluser]amrnt[/eluser]
Controller

Code:

function show($id = null)
{
$data['user'] = $this->user_model->getUserById($id))
$data['comments'] = $this->comment_model->getCommentsByTo($id);
$this->layout->view('users/show', $data);
}

Model::User

Code:

function getUserById($id = null)// gets a single user's data
{
$this->db->where('id', $id);
$query = $this->db->get('users');
return $query->row();
}

Model::Comment

Code:

function getCommentsByTo($to = null)
{
$this->db->orderby('id', 'desc');
$this->db->where('to', $to);
$query = $this->db->get('comments');;
$comments = array();
foreach($query->result() as $row){
        $comments[] = $row;
}
return $comments;
}

View::User : show.php

Code:

<h2>&lt;?=$user->id?&gt;</h2>
<h2>&lt;?=$user->name?&gt;</h2>
        
&lt;?php foreach($comments as $comment):?&gt;
from : &lt;?= $comment->from?&gt; to : &lt;?=$comment->to?&gt;
body : &lt;?=$comment->body?&gt;
<hr width="200" align="left" />
&lt;?php endforeach;?&gt;

$comment->to and $comment->from are IDs of users, i want to replace them with thier names

[eluser]amrnt[/eluser]
i can use this $user->name instead of $comment->to just in view:users "users/show.php"

Code:

from : <?= $comment->from?> to : <?=$user->name?>

but in other view:comments i can only use

Code:

from : <?= $comment->from?> to : <?=$comment->to?>

[eluser]Pascal Kriete[/eluser]
Ok, let's see here.

A note right off the bat, $query->result() returns an array of objects already, no need to create it again.

What you need to do here is two sql joins, with aliases for the users table, since it's ambiguous.

Code:

$this->db->select('comments.*, commenters.name, repliers.name AS reply_from')
$this->db->from('comments');

$this->db->join('users commenters', 'comments.name = commenters.id', 'left');
$this->db->join('users repliers', 'comments.reply_from = repliers.id', 'left');

$this->db->orderby('comments.id', 'desc');
$this->db->where('comments.to', $to);

$query = $this->db->get();

Untested, and a little complicated, but I think it should work. Gives you $comment->name and $comment->reply_from (felt bad using from since it's a sql action).

Try that and let us know how it goes.

[EDIT: fixed table name]

[eluser]Majd Taby[/eluser]
amrtami, to give you a more general answer, look up inner joins online. Here's what the SQL query would look like:

SELECT c.id,c.from,c.to,c.from,u.name FROM comments AS c
INNER JOIN users AS u ON u.id = c.id

That should work. The whole "AS" business just defines an alias so you don't have to keep typing comments/users.

[eluser]amrnt[/eluser]
i cant get it work !!, with your both solution..
what i want is looping the comments table. and in the place of from, to put their names - get it from users table..
can any body write a full example ?

again,

Code:

foreach ($comments as $comment):
echo 'commentID:'.$comment->id.' to:'.$comment->to.' from:'.$comment->from;
endforeach;

i need to get tow values from Users table, first for $comment->to, and other for $commeny->from .
i need a full example, please !,

Thanks all.