Query with multiple tables and foreach

[eluser]Unknown[/eluser]
Hello

I'm new to CI and the concept of MVC. I have some difficult to list a query with multiple tables and foreach in correct way.

Lets say I have three tables: Country - State - City

Example of an easy (for me) and wrong way to list these tables:

Controller

Code:

class Pagelist extends Controller {

    function Pagelist(){
          parent::Controller();

          $this->load->model('list_model');
    }

    function index(){

         $data['title'] = 'List';
         $data['country_list'] = $this->db->get('country');

        $this->layout->view('pagelist', $data);
    }
}

View

Code:

<!-- ========== List ===========  -->
|     <?php foreach($country_list->result() as $country): ?>
|        <?=$country->name?>
|      
|            <!-- 'Query' -->
|            <?php $this->db->where('country_id', $country->id); ?>
|            <?php $state_list = $this->db->get('state'); ?>
|          
|            <!-- ========== List ===========  -->
|            |    <?php foreach($state_list->result() as $state): ?>
|            |        <p>&lt;?=$state->name?&gt;</p>
|            |    
|            |         &lt;!-- 'Query' --&gt;
|            |         &lt;?php $this->db->where('state_id', $state->id); ?&gt;
|            |         &lt;?php $city_list = $this->db->get('city'); ?&gt;
|            |
|            |         &lt;!-- ========== List ===========  --&gt;
|            |         |    &lt;?php foreach($city_list->result() as $city): ?&gt;
|            |         |        <p>&lt;?=$state->name?&gt;</p>
|            |         |
|            |         |    &lt;?php endforeach ?&gt;
|            |         &lt;!-- ========== End ===========  --&gt;
|            |    
|            |    &lt;?php endforeach ?&gt;
|            &lt;!-- ========== End  ===========  --&gt;
|        
|    &lt;?php endforeach ?&gt;
&lt;!-- ========== End ===========  --&gt;

Since i can't make queries in view, how make the other 2 queries with comparison in a loop outside view? I tried to do a multiple array manually, but in the real case is so complicated to list.

Any ideas on an easy way to do?


Sorry for my poor english

[eluser]pistolPete[/eluser]
Since you are using a relational database, you should use its features.
Have a look at the SQL join statement: http://www.w3schools.com/Sql/sql_join.asp

[eluser]jedd[/eluser]
You might also want to post your db schema, and a rough idea of what kind of output you're looking to achieve.