[eluser]sqwk[/eluser]
I have two db tables "enquiries" and "emails" I would like to built a report that shows a paginated list of all enquiries with the relevant emails listed under each enquiry row.
How would I go about doing this in CI?. MySQL cannot handle this by itself as I need to get multiple rows from the emails table. (otherwise I would use correlated subqueries.)
I want to show the data in a nested table like so:
Code:
repeat this for all ENQUIRY rows {
/------------/-----------------/-----------------/-----------------/
/ Enquiry ID / Enquiry Field 1 / Enquiry Field 2 / Enquiry Field 3 /
/------------/-----------------/-----------------/-----------------/
repeat this for all EMAIL rows WHERE the email_enquiry_id = enquiry_id {
/----------/---------------/---------------/---------------/
/ Email ID / Email Field 1 / Email Field 2 / Email Field 3 /
/----------/---------------/---------------/---------------/
}
}
I thought of getting each enquiries and emails individually like so:
Code:
$this->load->model('enquiries');
$enquiries = $this->enquiries->get_enquiries($limit, $offset);
foreach($enquiries->result() as $row)
$enquiry_ids[] = $row->enquiry_id;
$enquiry_ids = array_unique($enquiry_ids);
$this->load->model('email_db');
$emails = $this->email_db->get_emails($enquiry_ids);
Where get_enquiries would get a selection of rows and get_emails only gets the rows that are linked to any of the enquiry rows.
How can I link the two together in a viewfile? Or even better built a nested array out of them?