CODEWITHSUNDEEP
phparrayscodeigniteractiverecord
Thaiscorpion
Thaiscorpion

Reputation: 479

How to query the database with infromation from another query?

I have a mysql table with information about students and another will info about payments. I want to display a table with the student name and payment info. Is my code correct? or am I misunderstanding how it works because this results in an empty array. I want it, for every student it found in $data['students'] to search for the payment info with same studentid be from this year and class id be the same as $id. Is there a simpler way to do this?

    $data['students'] = $this->db->get_where('students', array('class_id' => $id), 40);

    foreach ($data['students']->result_array() as $student){        
        $data['student_info'] = $this->db->get_where('student_payments', array('class_id' => $id, 'student_id' => $student['id'], 'year' => date("Y")), 40);        
    }

Thanks in advance,

Thaiscorpion.

Upvotes: 0

Views: 56

Answers (1)

Rick Calder
Rick Calder

Reputation: 18705

$query = "SELECT * FROM students
JOIN student_payments on student_payments.student_id = students.student_id
WHERE students.class_id= $id"
$result = my_sql_query($query);
if($result->num_rows() >0)
{
    return $result->result_array();
}  else  {
    return array();
}

I personally skip the CI stuff entirely once I start joining tables, sometimes it just makes things more difficult than they need to be. The only thing I didn't see in your code is how you're passing the class_id.

Upvotes: 1

Related Questions