Codeigniter combine two results

Question

I have two queries. I have to execute both queries and return in same result object. for example

$query1 = "SELECT name,age from students";
$query2 = "SELECT name,age from users";
$result1 = $this->db->query($query1)
$result2 = $this->db->query($query2)
 return result1 and result 2 together ;

I have to return the result of both queries in same object. please help me

the actual query is ::

SELECT dev_members.name,dev_members.id,dev_members.age,dev_members.family_id,dev_family.house_name,dev_ib_account_registration.account_id FROM (dev_members) 
JOIN dev_family ON dev_family.id=dev_members.family_id
JOIN dev_ib_account_registration ON dev_ib_account_registration.member_id=dev_members.id
           UNION
SELECT dev_members.name,dev_members.id,dev_members.age,dev_members.family_id, dev_family.house_name,dev_ib_sub_member_registration.account_id FROM (dev_members) 
JOIN dev_family ON dev_family.id=dev_members.family_id
JOIN dev_ib_sub_member_registration ON dev_ib_sub_member_registration.member_id=dev_members.id

score 1 · Answer 1 · answered Apr 13 '14 at 09:05

1

You can use UNION

$query = "SELECT name,age from students 
          UNION
          SELECT name,age from users";
$result = $this->db->query($query);

Or if you want to identify records from which table row belongs to then

$query = "SELECT name,age, 'students ' AS `table_type` from students 
          UNION
          SELECT name,age, 'users' AS `table_type` from users";
$result = $this->db->query($query);

answered Apr 13 '14 at 09:05

M Khalid Junaid

63,861
10
90
118

I have given an example query, I'll add the actual query by editing the question – user3387036 Apr 13 '14 at 09:06
@user3387036 using union in query will result all the records from both tables in same object.what you additionally need update your question accordingly – M Khalid Junaid Apr 13 '14 at 09:12

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

You might want to search for a question before asking it..

Here is the same question already with an answer.

edited May 23 '17 at 12:13

Community

1
1

answered Apr 13 '14 at 09:07

Anatoliy Kim

768
4
13

Hm... Try echo '
```
'; print_r($result1); print_r($result2); echo '
```
';
– Anatoliy Kim Apr 13 '14 at 09:13

Mudassir Hasan · Answer 3 · 2014-04-13T09:16:22.720

$query = "SELECT name,age from students UNION ALL SELECT name,age from users";

You need to use UNION ALL instead of UNION in your query

SELECT dev_members.name,dev_members.id,dev_members.age,dev_members.family_id,dev_family.house_name,dev_ib_account_registration.account_id FROM (dev_members) 
JOIN dev_family ON dev_family.id=dev_members.family_id
JOIN dev_ib_account_registration ON dev_ib_account_registration.member_id=dev_members.id
           UNION ALL
SELECT dev_members.name,dev_members.id,dev_members.age,dev_members.family_id, dev_family.house_name,dev_ib_sub_member_registration.account_id FROM (dev_members) 
JOIN dev_family ON dev_family.id=dev_members.family_id
JOIN dev_ib_sub_member_registration ON dev_ib_sub_member_registration.member_id=dev_members.id

UNION removes repeating rows in final result set while UNION ALL keeps the duplicate.

Codeigniter combine two results

3 Answers3