0

I am still learning php and I have to maintain a portion of my website. I need to combine two database tables to show only the latest 10 articles from two databases.

I know I have to add something after $articles = $this->db->query($query1 . ' UNION ALL ' . $query2); to return the results, but I can't figure out the code.

$this->db->select("id,article_type,title,main_img1,open_date");
        $this->db->from('column');
        $this->db->where('open_flg', 1);
        $this->db->where('open_date <= NOW()');
        $this->db->where('(close_date >= NOW() OR close_date = \'0000-00-00 00:00:00\')');
        $query1 = $this->db->get_compiled_select();
        $this->db->reset_query();
        
        $this->db->select("id,article_type,title,main_img1,open_date");
        $this->db->from('cases');
        $this->db->where('open_flg', 1);
        $this->db->where('open_date <= NOW()');
        $this->db->where('(close_date >= NOW() OR close_date = \'0000-00-00 00:00:00\')');
        $query2 = $this->db->get_compiled_select();
        $this->db->reset_query();
            
        $articles = $this->db->query($query1 . ' UNION ALL ' . $query2);
        
        $query = $this->db->query($articles . 'ORDER BY open_date DESC  LIMIT 10 ');
        $data['article_posts'] = $query->result_array();

P.S. The original code was only one database hence the following code worked fine:

$this->db->select("id,article_type,title,main_img1,open_date");
        $this->db->from('column');
        $this->db->where('open_flg', 1);
        $this->db->where('open_date <= NOW()');
        $this->db->where('(close_date >= NOW() OR close_date = \'0000-00-00 00:00:00\')');
        $articles = $this->db->get_compiled_select();

        $query = $this->db->query($articles . 'ORDER BY open_date DESC  LIMIT 10 ');
        $data['article_posts'] = $query->result_array();

If I replace the single database code for the UNION ALL one, I get the following error: Object of class CI_DB_mysqli_result could not be converted to string

Toru Kawahata
  • 15
  • 3
  • try just calling query once: `$query = $this->db->query($query1 . ' UNION ALL ' . $query2 . ' ORDER BY open_date DESC LIMIT 10 ');` – ysth Nov 28 '22 at 06:16
  • @ysth Thank you so much! This worked. Could you please post it as an answer?? – Toru Kawahata Nov 28 '22 at 06:28

1 Answers1

0

Yes that error can be possible because $articles variable is an query object. need to convert $articles variable into string like done in case of query1 and query2.

before below line $articles variable need to convert into string first-

$query = $this->db->query($articles . 'ORDER BY open_date DESC LIMIT 10 ');

Mahesh Kumar
  • 1
  • 1