[eluser]jeremydt[/eluser]
Just tried join; didn't work for me.
I think this should be all the information; thanks again for offering your help, I've just got no idea what to try next!
The two tables in question:
Code:
--
-- Table structure for table `problem`
--
CREATE TABLE IF NOT EXISTS `problem` (
`problem_id` int(11) NOT NULL AUTO_INCREMENT,
`problem_number` varchar(20) NOT NULL,
`problem_status_id` int(11) NOT NULL,
`problem_priority_id` int(11) NOT NULL,
`problem_title` varchar(255) NOT NULL,
`problem_detail` text NOT NULL,
`problem_approved_date` date DEFAULT NULL,
`problem_knownerror_date` date DEFAULT NULL,
`problem_closed_date` date DEFAULT NULL,
`problem_workaround` text,
`problem_rootcause` text,
`problem_solution` text,
`problem_isproactive` tinyint(1) NOT NULL DEFAULT '0',
`problem_related_incidents` int(11) DEFAULT NULL,
PRIMARY KEY (`problem_id`),
KEY `FK_problem_problem_status_problem_status_id` (`problem_status_id`),
KEY `FK_problem_problem_priority_problem_priority_id` (`problem_priority_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;
Code:
--
-- Table structure for table `problem_update`
--
CREATE TABLE IF NOT EXISTS `problem_update` (
`problem_update_id` int(11) NOT NULL AUTO_INCREMENT,
`problem_id` int(11) NOT NULL,
`problem_update_date` date NOT NULL,
`problem_update_text` text NOT NULL,
`problem_update_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`problem_update_id`),
KEY `FK_problem_update_problem_problem_id` (`problem_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
The Model
Code:
function retrieve_problem() {
$this->db->select('*');
$this->db->from('problem');
$this->db->like('problem_number', 'pbi');
$this->db->order_by('problem_approved_date', 'desc');
$this->db->join('problem_priority', 'problem_priority.problem_priority_id = problem.problem_priority_id');
$query = $this->db->get();
return $query;
}
function retrieve_updates() {
$result = $this->db->get('problem_update');
return $result;
}
The Controller
Code:
function index() {
$data['problems'] = $this->Model_data->retrieve_problem();
$data['updates'] = $this->Model_data->retrieve_updates();
// select the template (main_content) and load the view
$data['main_content'] = "report/view_default";
$this->load->view('includes/report_template', $data);
}
The View
Code:
<h2>Problem Investigations</h2>
<?
foreach ($problems->result() as $row) :
?>
<div class="problemRecord">
<a >problem_number;?>"></a>
<h3><?=$row->problem_title;?></h3>
<h4>Update: </h4><h5>Update text should appear here</h5>
<h4>Workaround: </h4><h5><?=$row->problem_workaround;?></h5>
<table class="problemTable">
<tr>
<th>ID#</th>
<th>Priority</th>
<th>Date Approved</th>
</tr>
<tr>
<td><?=$row->problem_number;?></td>
<td><?=$row->problem_priority_name;?></td>
<td><?=$row->problem_approved_date;?></td>
</tr>
<tr>
<td></td>
<td></td>
<td><?=daysDifference(date("Y-m-d"), $row->problem_approved_date);?> days ago</td>
</tr>
</table>
<a >problem_id;?>">Edit</a>
</div>
<?
endforeach;
?>