[FIXED] How to show the name from the "students" table that have the same faculty_id from "faculty" table?

Issue

i'm stuck with my PHP(CI+Bootstrap) homework code, i'll explain the (simplified) details of it:

1. There's a db called "university" with "students" and "faculty" tables
2. There's "faculty_id" in "students" table
3. In the "faculty_profile" view, there's a list of "students enrolled in this faculty"

Here's my MVC code:

Model

function get_data($table)
    {
        return $this->db->get($table);
    }

Controller

public function faculty_profile($id)
    {
        $where = array('faculty_id' => $id);
        $data['faculty'] = $this->db->query("select * from faculty f, students s where f.faculty_id=s.faculty_id and f.faculty_id='$id'")->result();

        $this->load->view('faculty_profile', $data);
    }

View (simplified)

<?php foreach($faculty as $f) { ?>
        <tr>
            <td><?php echo $f->faculty_name?></td>
        </tr>
        <tr>
            <td><?php echo $f->head_of_faculty?></td>
        </tr>
        <tr>
            <td>
                <b>Students enrolled in this faculty</b>
                <?php echo $f->students_name?>
            </td>
        </tr>
    <?php } ?>

The (simplified) result are below (I've got 2 rows enrolled in the same faculty):

------
Information Technology //faculty_name
Mr. Henry //head_of_faculty
Students enrolled in this faculty
Adam
------
------
Information Technology //faculty_name
Mr. Henry //head_of_faculty
Students enrolled in this faculty
Brian
------

Below it's what I wanted it to be:

------
Information Technology //faculty_name
Mr. Henry //head_of_faculty
Students enrolled in this faculty
Adam
Brian
------

The question is, where do I have to tinker with to get my desired result?

Oh, and I've tried putting 2 foreach(es) like this:

<?php foreach($faculty as $f) { ?>
        <tr>
            <td><?php echo $f->faculty_name?></td>
        </tr>
        <tr>
            <td><?php echo $f->head_of_faculty?></td>
        </tr>
        <?php foreach($faculty as $f) { ?>
        <tr>
            <td>
                <b>Students enrolled in this faculty</b>
                <?php echo $f->students_name?>
            </td>
        </tr>
        <?php } ?>
    <?php } ?>

And here's the result:

------
Information Technology //faculty_name
Mr. Henry //head_of_faculty
Students enrolled in this faculty
Adam
Brian
------
------
Information Technology //faculty_name
Mr. Henry //head_of_faculty
Students enrolled in this faculty
Adam
Brian
------

Thanks in advance, any help will always be appreciated

Solution

You mentioned that view "faculty_profile" only shows the student enrolled in this faculty. This means that it will only show 1 faculty. So you do not need to loop $faculty. Change your Controller Code as below.

        $data['faculty'] = $this->db->query("select * from faculty f where f.faculty_id='$id'")->result();
        $data['faculty']['students'] = $this->db->query("select * from students s where s.faculty_id='$id'")->result();
        $this->load->view('faculty_profile', $data);

Change your view as below

        <tr>
            <td><?php echo $faculty->faculty_name?></td>
        </tr>
        <tr>
            <td><?php echo $faculty->head_of_faculty?></td>
        </tr>
        <tr><td>Students enrolled in this faculty</td></tr>
        <?php foreach($faculty['students'] as $student ) { ?>
            <tr>
                <td>
                    <?php echo $student->students_name?>
                </td>
            </tr>
        <?php } ?>

Answered By - ascsoftw