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

Question

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.

"I decided to do multiple queries instead of a join because I will be adding additional columns to select from the second query." I don't understand why that might be a factor in deciding to make the correct choice of joining related table data while using (what I assume is) a relational database. This question seems to be an XY Problem that you shouldn't need to encounter. Also, your controller shouldn't be interacting with your database; that's the model's job. Time for a re-think. — mickmackusa
– mickmackusa ♦, Commented Apr 29 at 5:32

Ross · Accepted Answer · 2011-02-14 17:57:35Z

2

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

answered Feb 14, 2011 at 17:57

Ross

18k7 gold badges46 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

joshvermaire Over a year ago

Ross, thanks for your help. Your assumption of my first query returning multiple rows is correct. Sorry I didnt' explicitly state this. The database query ends up being SELECT event_name FROM (events) WHERE id = '2' AND id = '3' ` whereas I want it to be WHERE id = 2 OR 3.

joshvermaire Over a year ago

Sorry, now I'm probably really confusing you. I'm trying to make this work with both applications and events, thus the different database query here, but it's still supposed to be the exact same. It should read that the query ends up as SELECT app_name FROM (applications) WHERE id = '2' AND '3' -- instead I want it to be SELECT app_name FROM (applications) WHERE id = '2' OR id = '3'. How could I change the statement to have it correct this query? Sorry again for the confusion.

Ross Over a year ago

you can use $this->db->or_where(); for OR instead of AND more @ the docs

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related