[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><?=$row->priority_id?> Tutorials</h1>
<?php endif; ?>
<p>
<a >url)?>"><?=$row->title?></a>
</p>
<?php endforeach; ?>
<?php include 'global/footer.php'; ?>
?>
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.