CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
user3387036
user3387036

Reputation: 49

Codeigniter combine two results

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

Upvotes: 0

Views: 1900

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

$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.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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);

Upvotes: 1

Related Questions