[eluser]jpidgeon[/eluser]
I am trying to pull together info from 3 tables using the active record class and return this result to my controller.
I am using 3 tables: criteria, tasks and data
I feed in a "task_id" to the crtieria table, and a "qms_id" to the data table.
Both the data and criteria tables are joined via "criteria_id"
A "task_name" is also joined in via the tasks and criteria tables.
The issue I have, is that on every row there is data present on the criteria and tasks tables, but not the data table.
Data is only inserted to the data table when I update using the form, but I still need the controller to feed out all the info, even if some of the fields are coming back blank or not existant.
This is because "qms_id" doesn't exist on the data table every time, therefore the model is returning any results.
When I change the 2nd where clause to or_where it works correctly, but doesn't relat correctly to each task.
This seems quite complicated, but if anyone has any ideas how I can still pull back everything, everytime this it would be much appreciated?
MODEL:
Code:
function include_criteria($task_id, $qms_id)
{
$this->db->where('quality_criteria.task_id', $task_id);
$this->db->where('quality_data.qms_id', $qms_id);
$this->db->join('quality_tasks', 'quality_tasks.task_id = quality_criteria.task_id');
$this->db->join('quality_data', 'quality_data.criteria_id = quality_criteria.criteria_id', 'right');
$this->db->order_by('quality_criteria.criteria_order', "ASC");
$query = $this->db->get('quality_criteria');
$return['rows'] = $query->result();
$this->db->where('task_id', $this->uri->segment(3, 0));
$query = $this->db->get('quality_criteria');
$return['num_rows'] = $query->num_rows();
return $return;
}