I want to select data from 4 tables using the Codeigniter framework. The 4 tables have a similar column structure. I want to get the table data corresponding to certain year and month.
here is my table structure:
table t1
accid uid month year ccbalance
--------------------------------------------------------
101 19 May 1996 4545
-----------------------------------------------------
101 19 sept 1998 1500
--------------------------------------------------------
table t2
accid uid month year insbalance
--------------------------------------------------------
102 19 May 1995 2059
-----------------------------------------------------
102 19 july 1998 2500
--------------------------------------------------------
table 3
accid uid month year ccbalance
--------------------------------------------------------
109 19 June 1999 10000
-----------------------------------------------------
109 19 Aug 1990 1500
--------------------------------------------------------
table t4
accid uid month year ccbalance
--------------------------------------------------------
105 19 Aug 1995 10000
-----------------------------------------------------
105 19 May 1995 3333
--------------------------------------------------------
If I select May 1995, I want to get this result:
accid uid month year ccbalance
--------------------------------------------------------
105 19 May 1995 3333
102 19 May 1995 2059
Answer:
there is no built in union function in Codeigniter 2.0 and 3.0
you can create your own sql query and execute it like this:
$sql="(SELECT * from t1 where month='May' AND year=1995)
UNION
(SELECT * from t2 where month='May' AND year=1995)";
$query = $this->db->query($sql);
return $query->result();
more information on mysql union syntax you find here (official documentation)