Running SQL queries within query result loops in CodeIgniter

This took me a while to figure out but it's pretty obvious really. Initially I thought I'd have to run row-specific queries in the view itself (inside the results loop) but the solution is just to change where the result 'processing' happens from the view to the controller, allowing you greater control over what gets passed to the view in the first place.

Where you'd do something like this in plain old PHP:

$result = mysql_query("SELECT value FROM table");

while ($row = mysql_fetch_assoc($result)):

    mysql_query("SELECT id FROM table WHERE field='" . $row['value'] . "'");

endwhile;

in CodeIgniter you put the following code in your controller function:

$query = $this->Model->function($params);

$result = $query->result(); 
$foo = array();    

foreach ($result as $id => $object): 

    foreach ($object as $key => $value): 

        $foo[$id][$key] = $value;
    endforeach;
    $foo[$id]['bar'] = $this->function($params); 

endforeach; 

$this->data['foo'] = $foo;

$this->load->view('baz', $this->data);

then in the view:

foreach ($foo as $key => $row):

    print $row['item1'];

    print $row['item2'];

    print $row['bar'];

endforeach;

Basically, instead of passing the result of the main query direct to the view and looping through it there, you loop through the query results in the controller instead, and build up a two-dimensional associative array of results, to which you can add additional row-specific query results. Then in the view you just loop through your custom array.

Update:  You need to initialise the array ($foo = array()) before the foreach loop in the controller, otherwise if the query returns an empty result you'll get an error!