Basically i'm trying to build a comment system. The user looks at a photo on the site, and then can view all the comments made by other members.

Each comment will be looped out using a foreach (currently working fine), but what I need to do is then run a seperate query for each comment to get the user details of the user who posted it. These details are stored on a seperate database (otherwise i'd just do a join).

My model has this in it so far:

public function get_comment($id)
{
    $db_photos = $this->load->database('photos', TRUE);
    $db_photos->select('id, comment, userid, photoid');
    $db_photos->from('comments');
    $db_photos->where('photoid', $id);

    return $db_photos->get()->result();
}

And here's the controller:

public function view($id)
    {   

        $data['comment'] = $this->viewphoto_model->get_comment($id);
        if (empty($data['comment'])) { show_404(); }

        $this->load->view('templates/header', $data);
        $this->load->view('viewphoto/viewphoto', $data);
        $this->load->view('templates/footer', $data);
    }

And then the view:

        

        comment; ?>
        
        

So basically I need to grab the 'userid' value from each comment and then run a query on the 'users' database to get the user details for each comment posted.

Any help is most appreciated :)

EDIT:

Still not working, here's latest version.

Controller:

load->model('viewphoto_model');
    }


    public function view($id)
    {
        $data['photo'] = $this->viewphoto_model->get_photo($id);
        if (empty($data['photo'])) { show_404(); }

        $data['user'] = $this->viewphoto_model->get_user($data['photo']->userid);
        if (empty($data['user'])) { show_404(); }


        $comment = $this->viewphoto_model->get_comment($id);
        if($comment->num_rows() > 0)
            {
                foreach ($comment->result() as $r)
                {
                    $data['reg'][$i]['comment']=$r->comment;
                    $data['reg'][$i]['id']=$r->id;           

                   // Get user details from user table
                    $user_profile = $this->viewphoto_model->get_comment_user($r->userid);
                    if($user_profile->num_rows() > 0)
                    {
                        foreach ($user_profile->result() as $row)
                        {
                            // user details whatever you have in your db.
                            $data['reg'][$i]['id']=$row->id;
                            $data['reg'][$i]['firstname']=$row->firstname;
                            $data['reg'][$i]['lastname']=$row->lastname;
                        }
                    }

                    $i++;
                }
            }

        $data['title'] = $data['photo']->title.' by '.$data['user']->firstname.' '.$data['user']->lastname;
        $data['meta_description'] = $data['photo']->description;
        $data['directory'] = 'sub';

        $this->load->view('templates/header', $data);
        $this->load->view('viewphoto/viewphoto', $data);
        $this->load->view('templates/footer', $data);
    }
}

Model:

load->database('photos', TRUE);
        $db_photos->select('*');
        $db_photos->select("DATE_FORMAT(uploaddate, '%d/%m/%y') as uploaddate_formatted", FALSE);
        $db_photos->from('photos');
        $db_photos->where('approved', '1');
        $db_photos->where('id', $id);

        return $db_photos->get()->row();
    }

    public function get_user($userid)
    {
        $db_users = $this->load->database('users', TRUE);
        $db_users->select('id, firstname, lastname, email, type, type_staff, count_approved, count_sales, count_comments, count_editorial, featured, subscriber');
        $db_users->from('useraccounts');
        $db_users->where('id', $userid);

        return $db_users->get()->row();
    }


    public function get_comment($id)
    {
        $db_photos = $this->load->database('photos', TRUE);
        $db_photos->select('id, comment, userid, photoid');
        $db_photos->from('comments');
        $db_photos->where('photoid', $id);

        return $db_photos->get()->result();
    }


    public function get_comment_user($userid)
    {
        $db_users = $this->load->database('users', TRUE);
        $db_users->select('id, firstname, lastname');
        $db_users->from('useraccounts');
        $db_users->where('id', $userid);

        return $db_users->get();
    }

}

View:

        

         by