CodeIgniter getting data from database

Question

In my CodeIgniter project I'm getting the list of projects and successfully output them on a page. However, the data in one of the columns on that page should be retrieved from a different table in DB using the project ID. Could anybody help me to figure out how that can be done? Basically I need to make another query to that other table specifying the project id but don't actually know how to do that with CodeIgniter.

UPDATE

In the model I'm getting the list of projects with the following function:

function get_projects_list($page, $limit){
        $sql = sprintf("SELECT * FROM Project WHERE deleted != 1 LIMIT %d, %d", ($page-1)*$limit, $limit);
        $query = $this->db->query($sql);
        return $query->result();
    }

And in the controller I call the following function:

$projects_list = $this->Project_management_model->get_projects_list($curPage, self::$LIMIT_PER_PAGE);

        $data['projects_list'] = $projects_list;
        $data['cur_page'] = $curPage;
        $data['page_count'] = $pageCount;

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

And in the view I simply run on the $data with foreach and list the results in a table. In that table there's a column where I need to show a result from another table based on the ID of the project of that very row.

Thanks for helping.

Answer 1

You didn't mention whether you are using ActiveRecord or not. I am assuming that you are. I'll also guess that maybe what you need to do is use a JOIN.

If you were using straight SQL, you would do this using some SQL that might look something like this:

SELECT a.appointment_time, u.user_real_name FROM appointment a, site_user u WHERE u.site_user_id = a.user_id;

That would pull the user's name from the user table based on the user id in the appointment table and put it with the appointment time in the query results.

Using ActiveRecord, you would do something like this:

$this->db->select('appointment_time,user_real_name')->from('appointment')->join('site_user', 'site_user_id=appointment_user_id');

But why don't you tell us a little bit more about your question. Specifically, do you want this column in the other table to be related to the rows from the first table? If so, my JOIN suggestion is what you need.

Answer 2

I've actually found a way to do that with a custom helper. Creating a new helper and loading it in the controller gives an option to use the function from that helper in the view.

Thanks.

Answer 3

public function get data()
{
     $this->db->flush_cache();
     $query = $this->db->get('project_table');
     $result = $query->result();
     $data   = array();
             for ($i = 0;$i < count($result);$i++)
         {
              $data[$i] = $result[$i];
              $data[$i]['project_data'] = temp($result[$i]->id);
         }
         return data;
}

private function temp($id = 0)
{
$this->db->flush_cache();
$this->where('id',$id);
$query = $this->db->get('project_table2');
$result = $query->result();
if (count($result) != 0)
return $result[0]->data;
}

you can do it by some thing like that,or you can use sub-query by query function of database.