CODEWITHSUNDEEP
phpcodeigniter
Fahd
Fahd

Reputation: 353

Retrieving data from other tables in the view

I'm passing a set of objects to the view, including some foreign IDs to other tables in the database. Say objects of comments that have an attribute 'writer_id' which refers to the id of the user.

How can I display user's data (name for example) from the view ?

Here's the controller 'comments' :

function index() {

    $this->load->model('comment_model');
    $data['comments'] = $this->comment_model->get_comments();
    $this->load->view('comments',$data); }

and this is inside comment_model :

function get_comments()
{
    $id = $this->uri->segment(3); // ignore this , it's given
    $q = $this->db->query("SELECT * FROM comments WHERE post_id=$id");

    if ($q->num_rows() > 0 ) {
        foreach ($q->result() as $row){
            $data[] = $row;
        }

        return $data;
    }

}

All what I can use in the view is the 'writer_id' and not able to display his name or any other data.

UPDATE

here's the view file

    <?php
    if (is_array($comments)){ ?>

    <?php foreach ($comments as $comment) : ?>

    <p>
    <?php echo $comment->text;?> - written by <?php echo $comment->writer_id ?>
    </p>

    <?php endforeach ;?>
    <?php } ?>

This will print:

bla bla bla - written by 4

where it should be

bla bla bla - written by John (whose id is 4 as in user table)

Upvotes: 0

Views: 39

Answers (1)

Alexander Nenkov
Alexander Nenkov

Reputation: 2910

You can make a simple join.

SELECT comments.*, users.name as user_name FROM comments INNER JOIN users ON comments.writer_id = users.user_id WHERE post_id=$id

Then you will have the user_name in the result array.

Upvotes: 2

Related Questions