Sam Sam - 3 months ago 7
MySQL Question

Not able to fetch the entire data values from model to controller in codeigniter

I have 3 tables

student

id s_name
1 S1
2 S2


student_subject

id s_id subject
1 1 english
2 1 science
3 2 mathematics
4 2 poetry


teacher

id t_id s_id
1 1 1
2 1 2


I am trying to make a dashboard for a teacher where he can see all the students under him and the subjects that a student is following.
I have the id of the teacher which is being carried (as
t_id
) in the controller and then in the model, from there i am getting the id of the student (as
$s_id
) from teacher table and through this
s_id
i wish to get the details from student table and student_subject table.

The issues that i am facing is


1) In the model i am able to see all the details of student table but when i return the value to controller and then to view i get the detail of only 1 student.

2) In this model along with the student details i also wish to return the subjects that are under a particular student from student_subject table, however i don't know how to return 2 values(i.e student details and subject details) from 1 model to 1 controller and then to view


Code that i am following is

Controller

public function dashboard($t_id)
{
$data['student_request'] = $this->student_model->student_detail($t_id);
$this->load->view('teacher/dashboard_view',$data);
}


Model

public function student_detail($t_id)
{
$query = $this->db->query("SELECT * FROM teacher where t_id = $t_id");

foreach ($query->result_array() as $row)
{
$s_id = $row['s_id'];

$new_query = $this->db->query("SELECT * FROM student where id = $s_id");
$s = $new_query->result_array();
//print_r ($s); // just to check the data
return $s;
}
}


View

<?php
foreach ($student_request as $row)
{
echo $row['s_name'];
}
?>


Would really appreciate if anyone could help me with the problem

Sam Sam
Answer

Keep the controller code same and change the model and view

Model

$this->db->select('
        teacher.s_id,
        student.s_name,
        student_subject.subject
    ');

$this->db->from('teacher');
$this->db->join('student','student.id = teacher.s_id');
$this->db->join('student_subject','student_subject.s_id = teacher.s_id');
$this->db->where('teacher.t_id',$t_id);

$query = $this->db->get();
if($query->num_rows() < 1)
    {
        return FALSE;
    }

    return $query->result();

View

<?php if($student_request): ?>

    <?php foreach($student_request as $request): ?>

    <?php  
    echo $request->subject;
    echo $request->s_name;  

    ?>

    <?php  endforeach; ?>

    <?php endif; ?>
Comments