phpcodeigniterselectactiverecordresultset

Use a SELECT query's result set in another SELECT query without refactoring my CodeIgniter script


I’m doing the following queries in codeigniter and can’t figure out how to get the second query to work. How do I get an array of just the values from my first query and use that in my second?

function application()
{
    $user_id = $this->tank_auth->get_user_id();
    $this->db->select('app_id')->from('using')->where('user_id' , $user_id);
    $query = $this->db->get();

    $row = $query->result_array();

    if ($query->num_rows() > 0) :
    $this->db->select('app_name')->from('applications')->where('id' , $row['app_id']);
    $body['query'] = $this->db->get();
    endif;

    $this->load->view('apps_view', $body);

If I comment out the second query and var_dump($row); it gives me: array(2) { [0]=> array(1) { [“app_id”]=> string(1) “2” } [1]=> array(1) { [“app_id”]=> string(1) “3” } }

I decided to do multiple queries instead of a join because I will be adding additional columns to select from the second query.


Solution

  • Are you expecting the first query to return just one row?

    If so then you should use:

    $query = $this->db->get();
    
    $row = $query->row();
    
    $app_id = $row->app_id;
    //etc
    

    It's not clear from your question.

    If your first query returns (or can return) multiple rows, then you need to do this:

    $query = $this->db->get();
    
    if ($query->num_rows() > 0) :
        foreach($query->result_array() as $row)
        {
            $this->db->select('app_name')
                     ->from('applications')
                     ->where('id' , $row['app_id']);
        }
    
        $body['query'] = $this->db->get();
    endif;
    // etc
    

    you very may well need to adjust the code as I'm not sure what your desired result is. Do you see the difference?

    If you return result_array you have an array of results (fancy that!) - hence why your var_dump has [0]=> array(1) etc - there is an array for each row returned.

    If you are only wanting/expecting to return one result from the first query, you should use row instead.

    Hope this makes sense - should be a push in the right direction.

    edit In fact this might be the correct syntax:

    $query = $this->db->get();
    
    if ($query->num_rows() > 0) :
        $this->db->select('app_name')->from('applications');
        foreach($query->result_array() as $row)
        {
            $this->db->where('id' , $row['app_id']);
            // build the `where...` string with iteration/result/row
        }
    
        $body['query'] = $this->db->get(); // now we get the data.
    endif;
    // etc