Number of comments each post

[eluser]heitorcaldeira![/eluser]
Hey, whats up?
I'm doing a blog, and i need to know the number of observations recorded at each post, to put that number below each post. I know i do it with the "WHERE", but i can't do in the MVC pattern.
In table 'comments', I have the field 'id_post', with the post ID referring to the comment. Anyone know how to do this?
Example: (2) comments

(sorry for my english, i'm brazilian haha)

Thanks.

[eluser]mi6crazyheart[/eluser]
Are u using 2 tables for this system(one for post & other one for comments) ? if yes, then can u show us the DB table schema design. Without knowing these things it's quite not possible to give u exact solution. I hope u can understand...

[eluser]heitorcaldeira![/eluser]
Hey, thanks for reply.
Yes, i do this with 2 tables. The firts is 'posts'

CREATE TABLE `blog`.`posts` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`body` TEXT NOT NULL ,
`date` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

And the second table is 'comments'

CREATE TABLE `blog`.`comments` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id_post` INT( 11 ) NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
`text` TEXT NOT NULL
) ENGINE = MYISAM ;

That is, the 2 tables were constructed normally, because before i did so:

Code:

$sql = mysql_query("SELECT * FROM posts");
while($row = mysql_fetch_object($sql))
{
   echo "Post Name: " . $row->name;
   /*
   rest of code
   ...
   */
  
   //comments
   $sql2 = mysql_query("SELECT * FROM comments WHERE id_post = '" . $row->id . "')";
   $num = mysql_num_rows($sql2);
   echo "(" . $num . ") Comments in this post";
}

But i dont know how i do this with CI in MVC pattern.
Anyone can help me? :-)
Thanks !

[eluser]Krzemo[/eluser]
First of all why aint you using CI db class?!
Go trough manual, create proper model work with it in controller and display results in a view - otherwise quit CI.

If youre done with above:
Google "mysql count" and "mysql group by".
or
use CI Active Record's num_rows()
of
if you display comments just do php count() on results array or just add counter variable in foreach loop

theres way to many solutions to do that
All depends on your requirements, design, needs and performance.

[eluser]heitorcaldeira![/eluser]
Hey, you don't understand, haha. This example, is old. And yes, i use the CI db class.
I showed how i did before, hehehe. But i go try again width the num_rows()
Thanks.

[eluser]mi6crazyheart[/eluser]
I think u can make 2 model function for this. One model function for accessing u'r post information from "posts" table & other one is u'r number of comments that posts has got "comments" table.

EX:

Code:

getPostInfo($postID)
{
        $this->db->select('id,name,text');
        $this->db->where('id', $postID);    
        $query = $this->db->get('posts');
        return $query->result();
}

getCommentsInfo($postID)
{
        $this->db->select('id,name,text');
        $this->db->where('id_post', $postID);    
        $query = $this->db->get('comments');
        return $query->result();
}

From Controller u should call above to model function by providing u'r $postID(it is the 'id' field value from posts table).

EX:

Code:

$data['query1'] = $this->post_info->getPostInfo($postID) ; // MODEL function call for getting post info
$data['query2'] = $this->post_info->getCommentsInfo($postID) ; //MODEL function call for getting comments info of the post which "id_post" value match to the $postID value

$this->load->view('post_view',$data); // sending post info to VIEW page

Now, rest of things u can do u'e VIEW page.
EX: if u want to see how many comments u've got from u'r comments table use..

Code:

echo count($query2);

I hope u've got the idea. For rest, all the best...