MySQL query to get number of posts

[eluser]CodeIgniterNoob[/eluser]
Great it worked. Thank you all for your help.

Here is my function in the model:

Code:

function getTopCategories()
    {    
        $data = array();
        $this->db->select('c.*');
        $this->db->select('COUNT(p.id) as postsCount', TRUE);
        $this->db->where('parentid', 0);
        $this->db->join('posts p', 'p.category_id = c.id', 'left');
        $this->db->group_by('name');

        $Q = $this->db->get('categories c');
        
        if ( $Q->num_rows() > 0 )
        {
            foreach ($Q->result_array() as $row)
    
                $data[] = array
                (
                    'id' => $row['id'],
                    'name' => $row['name'],
                    'postsCount' => $row['postsCount']
                );
                
        }
        
    $Q->free_result();
    return $data;
    }

And here is my view:

Code:

if (count($cats))
    {
        foreach ($cats as $key => $list)
        {
            echo "<div class='recentposts'>".anchor("welcome/post/".$list['id'],$list['name'])."</div>\n";
            echo "<div class='postdetails'>".'Posts: (<span class="red">'.$list['postsCount']."</span>)</div>\n";
        }    
        
    }

Thank you all very much. I just learned alot about CI with this exercise, and will continue to learn.