[SOLVED] Codeigniter MySQL JOIN Query

Codeigniter MySQL JOIN Query

Im a beginner in MySQL, I got a hard time joining 2 tables in Codeigniter. So let me explain my tables.

In the first table named forum_topic I got these columns

topic_id topic_sk topic_subject topic_desc posted_by date_posted
1 123Qwe Rules and Regulations Anything Jonas Dulay 2015-11-03 11:15 PM

The second table is named forum_comment
comment_id topic_sk
1 123Qwe
2 123Qwe

The expected result will be like this
Topics Replies Date Posted by
Rules and Regulation 2 2015-11-03 11:15 PM Jonas Dulay

I dont know the process of getting the count of number of comment in other table
then join it . This is my query but it seems it will not help you.

    $this->db->select('*,count(forum_comment.topic_sk) as count');
    $this->db->from('forum_topic');
    $this->db->where(array('f_cat_id'=>$cat,'topic_status'=>1))
    ->join('forum_comment','forum_comment.topic_sk = forum_topic.topic_sk','left')
    ->group_by('forum_topic.topic_sk');

    $this->db->order_by('pinned',"DESC");
    $this->db->order_by("date_posted","DESC");

    $query= $this->db->get();
    return $query->result();

Solution

Please try below query, I think this is useful for you:

$this->db->select('ft.*,', FALSE);
 $this->db->select('IFNULL(COUNT(fc.topic_sk),0) as count', FALSE);
 $this->db->from('forum_topic as ft');
 $this->db->join("forum_comment as fc", ' rc.topic_sk = ft.topic_sk','left');
 $this->db->group_by('ft.topic_sk');
 $this->db->order_by('pinned',"DESC");
 $this->db->order_by("date_posted","DESC");

 $query= $this->db->get();
 return $query->result();

IFNULL check to value of column given, If column value is null then return count is 0. This is used for check column value null or not.

If you want to learn CI Please see this link: https://ellislab.com/codeigniter/user-guide/database/active_record.html