A query based off each former query result

[eluser]Alex Kendrick[/eluser]
Hi,

I'm working on my first CI project and enjoying the framework. I am also still somewhat new to PHP so it is possible I am missing something fundamental that is keeping me from being able to stick to MVC pattern. Here is what I am trying to do:

Query database for project information (name, id, etc), render it in the view (this I can do) BUT THEN, for each result, run a query based off one of the fields returned in the result (project id).

Here is my code. It works, but I have to put the second query in the view. There must be a way to have this all in the controller, but I'm stuck.

The Controller:

Code:

$data['query']= $this->db->query('SELECT gallery_text, gallery_id, proj_name FROM galleries, projects WHERE section_id=5 AND galleries.proj_id = projects.proj_id');

The View:

Code:

<?php foreach($query->result() as $row): ?>

  <p>&lt;?=$row->proj_name?&gt;</p>
  <p>&lt;?=$row->gallery_text?&gt;</p>
  
    &lt;?php // nested query for images based off each of the first query result gallery_id
    $img_query= $this->db->query("SELECT src FROM images WHERE gallery_id = $row->gallery_id");    
    foreach($img_query->result() as $row){
        echo $row->src .'<br />';
    }
    ?&gt;
    
  <hr/>
    
&lt;?php endforeach; ?&gt;

Can anyone point me in the right direction on how to accomplish the second query (the one nested in the loop in the view) inside the controller?

Thank you for any advice!

[eluser]kgill[/eluser]
Ok first things first, that query should really be in the model not the controller - the function of the controller is to decide what to do with the data coming from/going to models and views. Instead of returning the result set back to the controller to dump to the view, return an object or an array. Once you do that you'll have more control over how things work.

In the model you can then handle the images multiple ways, you can do the double query that you're doing now with the foreach and format the results into a multidimensional array (an array of arrays) or you combine the two queries into one (join the images table on the gallery id) and then return one big array or combine the queries and instead of just returning the resulting array loop through the results to produce your multidimensional array or any number of other ways depending on how creative you're feeling.