CI framework MySQL query

[epseix1]

I am using CI framework for the first time, and I need some help.

 

I am migrating an existing website over to the CI framework and have followed CI's tutorials step-by-step without much trouble, but there are a few roadblocks I can't seem to get past...

 

My database table has 2 columns - "country" and "city" - and I want to return a result in the view file that groups all rows by country, prints that country as a header, and then prints all corresponding cities as a list beneath the relevant country.

 

Ie.

 

<h1>England</h1>
<ul>
<li>Liverpool</li>
<li>London</li>
<li>Sheffield</li>
</ul>

Whereas before without CI framework, I could just write a query, return a result, redefine a variable and throw in a few if statements to achieve the desired result - the mvc structure of CI prevents me from doing so...

 

I gather the key to this mystery lies in the controller and creating some sort of array to manage the data and use in the view file - can somebody please help me with an example?

 

My existing controller is:

 

<?php

class Home extends CI_Controller {

 

public function index()
    {
		$this->load->model('Default_model');
		$data['query'] = $this->Default_model->getResults();
		$this->load->view('page_view', $data);
    }
}

Existing view file is:

 

<?php if (isset($query)):?>
<?php foreach ($query as $row):?>
<h1><?=$row->country?></h1>
<ul>
<li><?=$row->city?></li>
</ul>
<?php endforeach;?>
<?php endif;?>

And model:

 

function getResults()
    {
		$query = $this->db->query('SELECT * FROM events GROUP BY country, city ORDER BY country, city');
		return $query->result();
    }

Can anybody help me with an example, just to show me how this type of query is handled within the CI mvc structure?

 

Thanks!