Use if statement inside UNION ALL query on codeigniter

Toru Kawahata 0
Viewed 28

I currently have this code which is working fine, but I was wondering if I could streamline a little bit. I'm taking data from two tables, merging them with UNION ALL and adding an IF statement that matches the keywords the user input.

this is the current code:

 $data['keyword'] = $this->input->get_post('keyword');

    // 投稿取得
    /* SQL作成 */
    $this->db->select("id,article_type,title,main_img1,open_date,contents1");
    $this->db->from('column');
    if(!empty($data['keyword'])){
        $this->db->where('(title like \'%' . $data['keyword'] . '%\' OR description like \'%' . $data['keyword'] . '%\' OR contents1 like \'%' . $data['keyword'] . '%\') ' );
    }
    $query1 = $this->db->get_compiled_select();
    $this->db->reset_query();
    
    $this->db->select("id,article_type,title,main_img1,open_date,contents1");
    $this->db->from('cases');
    if(!empty($data['keyword'])){
        $this->db->where('(title like \'%' . $data['keyword'] . '%\' OR description like \'%' . $data['keyword'] . '%\' OR contents1 like \'%' . $data['keyword'] . '%\') ' );
    }
    $query2 = $this->db->get_compiled_select();
    $this->db->reset_query();
        
    $articles = $this->db->query($query1 . ' UNION ALL ' . $query2);

Is there anyway to put the if statement inside the UNION ALL query at the end? or do I have to repeat it each time? If not possible I guess I could make the IF statement a variable?

union-all codeigniter if-statement php
1 Answers

You can't generate a separate WHERE clause with get_compiled_select (it will add SELECT *), but you could write it manually and then concatenate it onto the compiled queries.

Something like this should work (not tested):

$data['keyword'] = $this->input->get_post('keyword');

// 投稿取得
/* SQL作成 */
$this->db->select("id,article_type,title,main_img1,open_date,contents1");
$this->db->from('column');
$query1 = $this->db->get_compiled_select();
$this->db->reset_query();

$this->db->select("id,article_type,title,main_img1,open_date,contents1");
$this->db->from('cases');
$query2 = $this->db->get_compiled_select();
$this->db->reset_query();

if(!empty($data['keyword'])){
   $where = ' WHERE (title like \'%' . $data['keyword'] . '%\' OR description like \'%' . $data['keyword'] . '%\' OR contents1 like \'%' . $data['keyword'] . '%\') ';
   $query1 .= $where;
   $query2 .= $where;
}
    
$articles = $this->db->query($query1 . ' UNION ALL ' . $query2);

But since your queries are so much alike, you could also write a helper function to generate the compiled queries. That would remove even more duplication:

private function get_compiled_select($table, $keyword) {
    $this->db->select("id,article_type,title,main_img1,open_date,contents1");
    $this->db->from($table);
    if(!empty($keyword)){
        $this->db->where('(title like \'%' . $keyword . '%\' OR description like \'%' . $keyword . '%\' OR contents1 like \'%' . $keyword . '%\') ' );
    }
    $query = $this->db->get_compiled_select();
    $this->db->reset_query();
    return $query;
}

And then use it like this:

$data['keyword'] = $this->input->get_post('keyword');

// 投稿取得
/* SQL作成 */
$query1 = $this->get_compiled_select('column', $data['keyword']);
$query2 = $this->get_compiled_select('cases', $data['keyword']);
    
$articles = $this->db->query($query1 . ' UNION ALL ' . $query2);
Marleen0
Related