How to count comments in MYSQL Query

[eluser]Ahmed Iqbal[/eluser]
Hello Friend,

i need help in my query

Code:

function home_get_post() {
    $this->db->from('post');
    $this->db->join('users','post.user_id = users.user_id');
    $this->db->join('categories','post.cat_id = categories.cat_id');
    $this->db->where('post_visible', '1');
    $this->db->order_by('post.post_added_date', 'desc');
    $this->db->limit(10);
    $query = $this->db->get();
        if ($query->num_rows > 0) {
            foreach ($query->result() as $row)
            $data[] = $row;
        }
        return $data;
    }

i've one more table as name of 'comments'. so i want to count comments on every post...!

comment table has foreign key 'post_id'.

Please, help in query i want, when for each loop show posts results comments number also display with it....!


thanks waiting for reply..

[eluser]Nick_MyShuitings[/eluser]
Avoid Active Record -> Acquire SQL using query bindings -> enjoy faster more robust database interactions

[eluser]Ahmed Iqbal[/eluser]
Please, explain....!
i'm logical weak in this case

[eluser]Nick_MyShuitings[/eluser]
IMHO: Active Records is a crutch for lazy or non talented programmers to have PHP auto magically generate SQL for them.

As such, it has limitations, like selects within selects. It sounds like what you need here is a (Select Count(*) FROM blah where blah=blah) as comment_count, within the main Select statement.

Do you know SQL? are you able to write it? if not I highly suggest studying up on it.