MySQL query to get number of posts

[eluser]CodeIgniterNoob[/eluser]
Hi, I have in one of my models, a function:

Code:

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

What Im trying to do is, get a number of live posts associated with each category. Just a number (4) for example.

Can anyone throw me in the right direction? Thanks.

[eluser]barbazul[/eluser]
Try this:

Code:

function getTopCategories()
    {
        $this->db->where('parentid',0);
        $qty = $this->db->count_all_results('categories'); // <----------------
        $Q = $this->db->get('categories')
        
        if ($Q->num_rows() > 0)
        {
            foreach ($Q->result_array() as $row)
            {
                $data[$row['id']] = $row['name'];
            }
        }
        
    $Q->free_result();  
    return $data;
    }

[eluser]Elliot Haughin[/eluser]
There's an elegant way to do it with one query too...

Code:

$this->db->select('c.*');
$this->db->select('COUNT(p.id) as postCount', TRUE);
$this->db->where('parentid', 0);
$this->db->join('posts p', 'p.categoryid = c.id', 'left');

$query = $this->db->get('categories c');

if ( $query->num_rows() > 0 )
{
    $categories = $query->result_array();
    
    // Should look like:
    //
    //
    // id: 3
    // name: blahblah
    // postCount: 15
        
}

I've not tested it, but that looks about right.

Elliot

[eluser]CodeIgniterNoob[/eluser]
Ok I modified the query that Elliot wrote, because before I was getting SQL errors.

So heres my function now and it works. But it works as before. It doesnt print number of posts.

Code:

function getTopCategories()
    {    
        $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[$row['id']] = $row['name'];
            }    
        }
        
    $Q->free_result();
    return $data;
    }

Ill continue working on this, but if any of you stops by, please drop a line. Thanks.

[eluser]barbazul[/eluser]
For what I understand, you just want to get the number and not the other data, so change the get() call for the count_all_results() call
It returns a single integer

http://ellislab.com/codeigniter/user-gui...ecord.html

Code:

$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');

        $qty = $this->db->count_all_results('categories');

[eluser]CodeIgniterNoob[/eluser]
Its not working. My function selects the categories table and gets the id and name fields. Then the controller prints the name of the category to a view, when you click on the category it then takes you to a page displaying all the posts that belong to that category.

I just want to add a number of published posts on the bottom of each category link, a number of posts which belong to that category.

[eluser]fesweb[/eluser]
NuclearArt: In your last example, you are not outputting the most important piece of information.

Code:

foreach ($Q->result_array() as $row)
{
    $data[$row['id']] = $row['name'];
}

You have to pass the resulting postsCount from the query to your data, or it will never work.

Code:

foreach ($Q->result_array() as $row)
{
    $data[$row['id']] = $row['name'].' has this many posts: '.$row['postsCount'];
}

[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.