Relational databases and 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.

Answer 2

Joining the ideas in the previous answers, you could use the query builder (using CI 3 name) to join the two tables and return all the information you need from that method in the model:

public function get_projects() {
    $this->db->from('project_table');
    $this->db->join('adress_table', 'adress_table.project_id', 'project_table.id');

    return $this->db->get()->result_array();
}

You can learn more about the QueryBuilder Class at the documentation.

Answer 3

Ideally you want to keep all your database queries in the model. You can call other functions in your model by using $this->function_name().

I believe this will achieve what you are after (these go in your model):

// Function to read all projects from database
public function get_projects() {
    $results = $this->db->get('ed_projects')->result();
    foreach($results as $r) {
        $r->address = $this->get_address($r->id);
    }
    return $results;
}

// Function to read addresses for a $project_id
private function get_address($project_id) {
    return $this->db->from('project_address_table')
                ->where('project_id', $project_id)
                ->get()->result();
}

I would also recommend using the codeigniter active record class (http://www.codeigniter.com/user_guide/database/active_record.html) for doing easy database queries like this as it makes it a lot easier to see what your query is doing