PhP (Codeigniter) Merging query results before foreach loop

Question

I would like know if it is possible to merge sql queries like the following from codeigniters active record.

   //get assigned contacts    
      $this->db->select('*, CONCAT(first_name, " " ,last_name) AS name', FALSE);
      $this->db->from('customers_contacts');
      $this->db->join('customers_accounts', 'customers_accounts.contact_id = customers_contacts.contact_id');
      $this->db->like('CONCAT(first_name, " " ,last_name)', $q);
      $results1 = $this->db->get();
    //get non assigned contacts
      $this->db->select('*, CONCAT(first_name, " " ,last_name) AS name', FALSE);
      $this->db->from('customers_contacts');
      $this->db->like('CONCAT(first_name, " " ,last_name)', $q);
      $results2 = $this->db->get();

I tried using $query = array_merge($results1, $results2); but that does not work, I believe because ->get() is returning an array of objects.

So I got it to work by putting both through a foreach loop, and then merging the resulting arrays. But I need to do some conditionals that would be easier in one foreach loop than two.

Sorry I should have been clearer, because both queries are on the same table, only one being joined with a junction table, (assigned vs unassigned contacts) there are extra columns being returned in the assigned contacts. So union won't work. — skribe, Sep 12 '15 at 05:00
I think you need this: http://stackoverflow.com/questions/4345554/convert-php-object-to-associative-array — Insane Skull, Sep 12 '15 at 05:09

score 2 · Answer 1 · answered Sep 12 '15 at 11:52

2

You can use like below

$arr_results1 = $this->db->get()->result_array();
$arr_results2 = $this->db->get()->result_array();
var_dump(array_merge($arr_results1,$arr_results2));

Hope this will help you!

answered Sep 12 '15 at 11:52

Kausha Mehta

2,828
2
20
31

score 1 · Answer 2 · answered Sep 12 '15 at 05:18

After a bit of reading I came up with the following and it works!

//first query here...
$results1 = $this->db->get()->result_array();
//second query here...
$results1 = $this->db->get()->result_array();
//then...
$query = array_merge($results1,$results2);

score 1 · Answer 3 · answered Sep 14 '15 at 21:16

You must always do $results->result(); to get an array of rows after $results = $this->db->get();. note: $results->result(); is an array of objects.

For example

# ...
$results1 = $this->db->get();

# ...
$results2 = $this->db->get();

$results = array();

if ($results1->num_rows()) 
{
    $results = array_merge($results, $results1->result());
}

if ($results2->num_rows())
{
    $results = array_merge($results, $results2->result());
}

return $results;

This will return an array of objects (rows) and you iterate through data as usual:

foreach ($results as $row) 
{
    echo $row->id;
    echo $row->column_name_1;
    # and so on...
}

PhP (Codeigniter) Merging query results before foreach loop

3 Answers3

Linked