Calling the database from a view file?

[eluser]timsby[/eluser]
I am adding some features to a new blog system i am building from scratch. I'm using it as an opportunity to learn more about MVC and Ci. I want to display how many comments a particular post has and the only way I have figured out is by calling a database record from the view file like such...

Code:

<?php
    $this->db->where('entry_id', $row->id);
    $comments_num = $this->db->count_all_results('comments');
?>

<p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments ('.$comments_num.')');?&gt;</p>

I've read my explanation of the MVC functionality and shouldn't i be using a model to handle this for me? If so how? I haven't been able to figure out an alternative method. Thank you in advance for any advice!

[eluser]TheFuzzy0ne[/eluser]
CodeIgniter doesn't force you not to use models in your views, however, I do believe calling the upon the database directly from inside a view goes against the whole "MVC" thing. Generally, it's the controller that's the link between the model/database and the view. It gets it's information from models or user input and then sets the variables for the view before loading it. I would recommend that you export that function to a model. It's the logical place to put it, and may save you some head scratching 6 months from now when you want to develop your application further.

Hope this helps.

[eluser]timsby[/eluser]
Sorry but not really i'm a little more confused now, how would i develop this into a model to use on a more regular basis bearing in mind the above code is located inside a blog entries foreach loop, it gets the row id of the entry and uses it to count how many corresponding comments there relating to that row id inside the comments table.

Code:

<?php foreach($query->result() as $row): ?>

<?php
    $this->db->where('entry_id', $row->id);
    $comments_num = $this->db->count_all_results('comments');
?>

<h3>&lt;?=$row->title?&gt;</h3>
<p>&lt;?=$row->body?&gt;</p>

<p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments ('.$comments_num.')');?&gt;</p>

<hr>

&lt;?php endforeach; ?&gt;

[eluser]TheFuzzy0ne[/eluser]
Personally, I would move the logic out of the view, and have it in the controller. The controller is the place to process the data received from the models, and perhaps catch any potential problems and take the appropriate action. The controller can create an array of objects that can be passed in to the view. In my humble opinion, a view should simply format your output, and the data should be fed in as variables.

Of course, this only applies if you are trying to stick to the MVC pattern, which means that you should keep your presentation layer separate from your data layer. Otherwise, you're encouraged to use CodeIgniter in whatever way makes your life easiest.

I hope this helps.

[eluser]timsby[/eluser]
thanx for the response and you have helped! Could you be so kind as to give me a hint at how i might dynamically count the number of comments for a blog post via a method in the controller? I'm racking my brains as to how this could be possible but coming from outside a OOP background i'm pretty stuck

Cheers!

[eluser]TheFuzzy0ne[/eluser]
In your position, I would create function in my model specifically for getting a page of data. I would use an SQL statement to join the tables together, and count the comments into their own column. This would mean that you get to do the business within one query. You'd need to use $this->db->distinct() and $this->db->group_by() to get it the way you want it, and you'd need to use the SQL COUNT() function in the select part of your statement.

Does this help at all?

[eluser]kgill[/eluser]
You're over-thinking this... Instead of returning the query object from your model, return the data you actually need either as an array or an object. This means you process the result object in your model and then you're free to do what you need to do such as your looping through the results and running a 2nd query to add in the number of comments.

However, the simpler solution would be to select what you want from the start, join the two tables (blog posts & comments) and select the posts + a count of comments grouped by the blog posts in your model.

[eluser]SitesByJoe[/eluser]
You could use a join or run a separate query to get the comments and store the num_rows() of the query and pass it to your view.

[eluser]obiron2[/eluser]
OK,

Before you got to your view, you must have built and array of posts.

1st improvement:
In the view you are looping through the posts array and fetching the number of comments back as a query. You could do this just as easily in the controller and add a new element to each posts array/object which is the number of comments and then use that in your view. This has 2 benefits. The person maintaining the view doesn't have to know anything about the database structure and the view should render faster as the data is pre-fetched.

2nd improvement:
There is a direct relationship between the post and the number of comments so you could get the number of comments while you are getting the blog posts with a simple join and return the count of the posts as part of the data array. This requires less trips to the database and could be significantly faster. IF you wanted to show the comments as well, this is a different issue as there will be n comments for each post and you need a nested array.

obiron