关系数据库和CodeIgniter

Question

I'm using CodeIgniter to build a website, and I want to show a list of construction projects from a database table, which we will simply call project_table. For each project I also have an address, stored in another table, address_table, each address has a project_id, which links it to a project.

I have made a function, get_projects, in my projects model, which is used to get the project information and pass it to the project view, like such:

public function index() {
    $data['projects'] = $this->project_model->get_projects();
    $data['title'] = 'Ejendomme';
    $this->load->view('templates/header', $data);
    $this->load->view('projects/index', $data);
    $this->load->view('templates/footer');
}

My question is how I get the addresses read, linked to the correct projects, and shown. I suppose I could make a function which is called from the view, which loads the address based on project_id, but as I understand it, this is really bad practice. Is there a way to call a get_address function from the controller, and pass it on to the view, without losing track of which address belongs to which project?

Update: Per request here is the function get_project(), which gets the project information from the database. I have considered calling a get_address() function inside this, but I am not sure how I would return the addresses from the function.

// Function to read all projects from database
public function get_projects() {
    $query = $this->db->get('project_table');
    return $query->result_array();
}

Answer 1

Was more useful if you've posted the get_projects method from models. Anyway, the trick is to make use of Model-View-Controller(MVC) architecture, therefore you put into the model the selection from database. Here is an example with a method to extract your data from those two tables:

public function get_projects()
{
//for standard mySQL
//select only the db fields that you need
$query = "SELECT pt.*, at.* FROM project_table as pt, address_table as at WHERE pt.project_id = at.project_id";
$db_result = $this->db->query($query);
$result_object = $db_result->result();
/*
here you can add a check for the result (for instance to check if the return is not empty)
*/
return $result_object;
}

Now parse the result to a view and play from there with the data.