Welcome Guest, Not a member yet? Register   Sign In
Please assist with foreach/array question
#1

[eluser]jeremydt[/eluser]
Hi,

I'm absolutely stumped at my latest challenge. Hoping someone can point me in the right direction.

I have a model which retrieves rows from a table and returns them to the controller. The controller then passes them to the view and then the view has a foreach loop to echo each of the rows in a user friendly format. All fairly simple so far....

The problem for me comes when I want to echo the rows from another table that relate to the output of the foreach statement above withinthe output above. The other table has a foreign key to the primary key of the table I mentioned above. So, from what I would gather (and this might be a wrong assumption) I would have a foreach loop to echo these results inside the foreach loop (the one I mentioned above) to output all the rows from the second table that have the same id as the one that is being output via the first foreach loop. The problem for me is that I can't think of how I would go about doing that.

Clearly, I pass the second tables values to the view from the model via the controller. So perhaps the other way for me to do it is to loop through the array and find values the key that match the primary key of the one being output. Unfortunately I haven't had much experience with arrays and wouldn't be sure what function I should be using if I was to try this. The array of the second table looks something like this via print_r

Code:
CI_DB_mysql_result Object
(
    [conn_id] => Resource id #30
    [result_id] => Resource id #39
    [result_array] => Array
        (
        )

    [result_object] => Array
        (
        )

    [current_row] => 0
    [num_rows] => 1              [b]// There would normally be more results than this[/b]
    [row_data] =>
)
Code:
Array
(
    [0] => Array
        (
            [problem_update_id] => 8
            [problem_id] => 9
            [problem_update_date] => 2010-09-09
            [problem_update_text] => Some Update
            [problem_update_active] => 0
        )

)
#2

[eluser]Bainzy[/eluser]
why not use a JOIN statement on the two tables what you want the data from. so say you have a products table and a details table. If you joined the two tables by product_id ... then you shall only need 1 array of data to pass to your view.

Then you can use your foreach loop as normal and only require one.

If you look in the user guide for the join statement and if you still need further help i will be happy to post some example code and guide you.

chris
#3

[eluser]jeremydt[/eluser]
[quote author="Bainzy" date="1286817352"]why not use a JOIN statement on the two tables what you want the data from. so say you have a products table and a details table. If you joined the two tables by product_id ... then you shall only need 1 array of data to pass to your view.

Then you can use your foreach loop as normal and only require one.

If you look in the user guide for the join statement and if you still need further help i will be happy to post some example code and guide you.

chris[/quote]

The issue I had with join (and I already use join to grab some other associated fields in other tables) is that the second table contains multiple values corresponding back to the same PK and I didn't think join could handle it.

Thanks so much for your offer, I'll post some code shortly.
#4

[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>
&lt;?
foreach ($problems->result() as $row) :
?&gt;
<div class="problemRecord">
<a >problem_number;?&gt;"></a>
<h3>&lt;?=$row->problem_title;?&gt;</h3>
<h4>Update: </h4><h5>Update text should appear here</h5>
<h4>Workaround: </h4><h5>&lt;?=$row->problem_workaround;?&gt;</h5>
<table class="problemTable">
    <tr>
        <th>ID#</th>
        <th>Priority</th>
        <th>Date Approved</th>
    </tr>
    <tr>
        <td>&lt;?=$row->problem_number;?&gt;</td>
        <td>&lt;?=$row->problem_priority_name;?&gt;</td>
        <td>&lt;?=$row->problem_approved_date;?&gt;</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td>&lt;?=daysDifference(date("Y-m-d"), $row->problem_approved_date);?&gt; days ago</td>
    </tr>
</table>
<a >problem_id;?&gt;">Edit</a>
</div>
&lt;?
endforeach;
?&gt;
#5

[eluser]Bainzy[/eluser]
ok that seems like enough to go on .. but i am at work atm and dont have time to work it out lol ... iwill post when i get home about 5PM UK time.

When you say the join did not work for you ? .... what problems did you get whilst using the JOIN ?

Chris
#6

[eluser]jeremydt[/eluser]
I've just arrived at work (Aussie time) I'll grab some output from the join statement and post it here shortly.

Effectively, it echo'd nothing.
#7

[eluser]jeremydt[/eluser]
OK, so I added this into the query

Code:
$this->db->join('problem_update', 'problem_update.problem_id = problem.problem_id');

The problem is that if an update isn't present, the row doesn't echo at all. And, if there are multiple updates (second table - the one we've joined) per problem (first table, the one we've selected from) then, in the view, the foreach loop echo's the same row multiple times (according to the number of updates in the second table).
#8

[eluser]techgnome[/eluser]
Make it a left join... add ",'left'" after your join condition.

-tg
#9

[eluser]jeremydt[/eluser]
[quote author="techgnome" date="1286860810"]Make it a left join... add ",'left'" after your join condition.

-tg[/quote]

Thanks, this ensured that the ones that didn't have updates still echo'd out. I'll have to research this later on so I can understand the difference.

It hasn't resolved the issue of the row's echoing multiple times when multiple updates exist.
#10

[eluser]jeremydt[/eluser]
I got around this by breaking the MVC pattern; I'm sure some of you will hunt me down for this!

The Model
Code:
function retrieve_related_updates($problem_id) {
        $result = $this->db->get_where('problem_update', array('problem_id' => $problem_id));
        return $result;
}

From the view
Code:
<h4>Update: </h4>
            <h5>
            &lt;?
            $this->load->model('report/Model_data');
            $updates = $this->Model_data->retrieve_related_updates($row->problem_id);
            foreach ($updates->result() as $update) :
            echo '<b>' . $update->problem_update_date . ':</b>';
            echo ' ';
            echo $update->problem_update_text;
            echo '<br />';
            endforeach;
            ?&gt;
            </h5>




Theme © iAndrew 2016 - Forum software by © MyBB