Help with code bloat and SQL...and oh my just help :(

[eluser]naez[/eluser]
*sigh* I don't know where to begin with this.

Basically I'm trying to write a CI app that displays a set of tutorials. I want this done in a specific way. I will provide the output I am expecting first, my table design, and my code. PLEASE make suggestions on anything!

OKAY! Here's what I would like to see output from my view.

MY DESIRED OUTPUT:

Code:

<h1>Novice:</h1>
- Novice Category 1 (2 articles)
<h1>Intermediate</h1>
- Intermediate Category 1 (5 articles)
- Intermediate Category 2 (2 articles)
- Intermediate Category 3 (20 articles)
<h1>Advanced:</h1>
- Advanced Category 1 (2 articles)

Basically, Novice, Intermediate, and Advanced are parent categories. Then, I would like to see the number of articles each sub-category has. Seems simple, but I'm lost.


Here's what I've got so far:

controller

Code:

<?php
function index()
{
    $data['tutorials'] = $this->tut_model->get_all_tutorials();
    $this->load->view('tutorial_index', $data);
}
?>

Model

Code:

<?php
function get_all_tutorials()
{
    return $this->db->get($this->tut_table_cat_view);
        // view concatenating table categories and tutorials
        // ...for now, needs a rewrite... details on these tables below
}
?>

View

Code:

<?php include 'global/header.php'; ?>
    <?php
        $old = '';
        foreach ($tutorials->result() as $row):
            if ($old != $row->priority_id):
                $old = $row->priority_id;
               // basically, if there's a new priority_id, we need a new header!
         ?>
                <div class="space"></div>
                <h1>&lt;?=$row->priority_id?&gt; Tutorials</h1>
            
    &lt;?php     endif;     ?&gt;
                <p>
                    <a >url)?&gt;">&lt;?=$row->title?&gt;</a>
                </p>
    &lt;?php     endforeach;     ?&gt;
&lt;?php include 'global/footer.php'; ?&gt;

?&gt;

Basically my table structure is like this right now:

Code:

CATEGORIES
-+-------------------+-
`category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(255) DEFAULT NULL,
  `priority_id` enum('Novice','Intermediate','Advanced') DEFAULT 'Novice',
  
PRIMARY KEY (`category_id`),
KEY `priority_id` (`priority_id`)

--

TUTORIALS
-+------------------+-
  `tut_id` int(11) NOT NULL AUTO_INCREMENT,
  `author_id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` longtext,
  `url` varchar(255) DEFAULT NULL,
  `cat_id` int(11) DEFAULT '2',

PRIMARY KEY (`tut_id`),
KEY `author_id` (`author_id`),  #references user_table
KEY `cat_id` (`cat_id`)  #references categories table

The priority_id enum will never change, I'm not worried about the flexibility on that... here's what I used to create the view seen in my model:

Code:

CREAT VIEW all_tutorials AS
SELECT * FROM categories c, tutorials a WHERE a.cat_id = c.category_id GROUP BY priority_id

SO! Now I ask you all... what's the best, efficient and correct way to achieve the results I'm looking for (see above)? I assume I need a COUNT() in there somewhere but I'm so lost

Thanks in advance to anyone who helps me with this.