how to union all this query code in codeigniter

Question

rjgapz 0 Newbie Poster

9 Years Ago

      $this->db->select('referral1.*, client.*, employee.*');
      $this->db->from('client');
      $this->db->join('referral1', 'client.referral_id = referral1.referral1_id', 'inner' );
      $this->db->join('assign_psychotherapist ', 'assign_psychotherapist.a_referral_id = client.referral_id', 'inner' );
      $this->db->join('employee ', 'assign_psychotherapist.a_psychotherapist_id  = employee.empid', 'inner' );
      $this->db->where("referral_status ='Assigned' OR referral_status ='Accepted' ");
      $this->db->order_by("referral_date", "desc");



      $this->db->select('referral1.*, client.*, volunteer.*');
      $this->db->from('client');
      $this->db->join('referral1', 'client.referral_id = referral1.referral1_id', 'inner' );
      $this->db->join('assignvolunteer', 'assignvolunteer.Vreferralid = client.referral_id', 'inner' );
      $this->db->join('volunteer', 'assignvolunteer.Vvolunteerid = volunteer.volid', 'inner' );
      $this->db->where("referral_status ='Assigned' OR referral_status ='Accepted' ");
      $this->db->order_by("referral_date", "desc");

codeigniter php sql

2 Contributors
1 Reply
4K Views
16 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by mangel.murti

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mangel.murti 5 Helps you in some way · Answer 1 · 2015-04-02T06:23:12+00:00

I used union in my one of project. Union requied columun must be same in both queries.hope it helps you.

function get_invoice_data($start, $limit, $user_id, $flag_total_count = "NO", $package) {
        $q_part = '';
        if ($flag_total_count != 'YES')
            $q_part .=" limit " . $start . " , " . $limit . " ";
        $q = "select tts.license_type,tt.multi_domain_license,tt.developers_license,tts.theme_transaction_id,tt.theme_price,tts.transaction_id,tts.amount,tts.service_type,tts.theme_id,tt.theme_title,tt.theme_price,tts.created_date from theme_transactions tts inner join theme_themes tt on tt.theme_id=tts.theme_id where tts.user_id=" . $user_id . " and tts.theme_id !=0 group by tts.theme_transaction_id " . $q_part . " union select tt.multi_domain_license,tt.developers_license,tts.license_type,tts.theme_transaction_id,tt.theme_price,tts.transaction_id,tts.amount,tts.service_type,tts.theme_id,tt.theme_title,tt.theme_price,tts.created_date from theme_transactions tts inner join theme_themes tt where tts.user_id=" . $user_id . " and tts.theme_id =0 group by tts.theme_transaction_id" . $q_part . "";
        $query = $this->db->query($q);
        if ($query->num_rows() > 0)
            return $query->result();
        else
            return NULL;
    }