How to display num_rows() and result() in the same Foreach loop

[eluser]Gwarrior[/eluser]
As the title says, I have a custom blog that I've made, and under posts, users are able to select a category. In the side panel, it lists the current categories pulled from a DB Query. In example:

View:

Code:

<ul>
<li>[category here] ([number of posts with that category here])</li>
</ul>

I want the <li>'s to foreach loop.

How would I go about doing this? What I have now is not even close, so I won't even post it.

Thanks!

[eluser]InsiteFX[/eluser]
Hi,

Please next time show your code for your foreach, it's kind of hard to help without it.

But you may be able to do this:

Code:

$cat_count = 0;
$my_cat = "";

foreach (your foreach)
{
    $my_cat = $your_cat;
    if ($your_cat == $my_cat)
    {
        // increment cat count        
        $cat_count++;
    }
    else
    {
        // new cat so reset $cat_count        
        $cat_count = 0;
    }
}

Like I said above without seeing your code it's a little hard to help.

the above should give you a clue of what you need to do.

Enjoy
InsiteFX

[eluser]kgill[/eluser]
In your query left outer join your categories table to your posts table, selecting a count of posts and the category, group by category. That will give you data like so:

Code:

catname postcount
------- ---------
cat1    12
cat2    4
cat3    0

Then just loop through the rows in a foreach displaying row->catname (row->postcount) in your li's.

[eluser]Gwarrior[/eluser]
Thanks for the replies, guys.

So for the left outer join, where 'blog_system' = post table name, and blog_categories = post category table, I should do it roughly as such? Below is what I have tried, and it is almost working, but its mixing categories and blog posts I believe. Could you help clean this up to work? Sorry... first left outer join.

Code:

function get_num_category() {
        $query = $this->db->query('SELECT category AS cat FROM blog_system LEFT OUTER JOIN blog_categories USING (category)');
        $query = $query->result();
        return $query;
    }

[eluser]Gwarrior[/eluser]
Mispost.