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.

[eluser]OES[/eluser]
You can achive your counts with SQL. Just look up mysql COUNT in google. And you would need GROUP also.

You can still use Active recored to get your results.

Here is an example I used to count comments per news ID.

Code:

function list_entries($limit, $cat='', $pag='', $app)
    {
        $this->db->select("ci_newsentries.*, ci_newscategories.newscategory_title AS catname, ci_newscategories.newscategory_title_sef AS caturl, COUNT(ci_newscomments.newscomment_id) AS no_comments");
        $this->db->from('ci_newsentries');
        $this->db->join('ci_newscomments', 'ci_newsentries.newsentry_id = ci_newscomments.newscomment_newsentry_id', 'left');
        $this->db->join('ci_newscategories', 'ci_newsentries.newsentry_category = ci_newscategories.newscategory_id', 'left');
        $this->db->where('ci_newsentries.newsentry_approved', $app);
        if ($cat):
            $this->db->where('ci_newsentries.newsentry_category', $cat);
        endif;
        $this->db->group_by("ci_newsentries.newsentry_id");
        $this->db->order_by("ci_newsentries.newsentry_date", "desc");
        $this->db->limit($limit, $pag);
        $query = $this->db->get();
        return $query->result_array();
    } // list_entries()

This should hopefully get you on your way.