Help Counting the comments from a table

[eluser]Miguel Diaz[/eluser]
Hi I am making a website in which I list a hotel catalogue and I need to add in each hotel the number of comments that I have in other table but I dont know how to do it can someone help me please, I m new in php and Codeigniter.

This is the controller I m using to list the hotels.

function index()
{
$data['clientes'] = $this->data_model->cargarHoteles();

$this->load->view('includes/tags', $data);
$this->load->view('includes/header', $op);

//template general//
$data['content'] = 'hotels-view';
$this->load->view('includes/template', $data);
}

This is my datamodel

function cargarHoteles(){
$q = $this->db->query("select
hd.data_id as 'Registro',
hd.text as 'Contenido',
hd.smalltext as 'SmallContent',
hh.url as 'Url',
hn.name as 'Name',
hn.category as 'Categoria',
hn.destination_url as 'DestinoUrl',
hn.destination as 'Destino',
hn.location as 'Locacion',
rb.01 as 'Precio'
from hoteldata hd, hotelnames hn, ratesBestDay rb, hotellinks hh, c comments
where rb.id=hn.id
and hn.id=hd.data_id
and hd.data_id=hh.link_id
and hh.link_id=c.hotelcode
");
if($q->num_rows() > 0) {
foreach($q->result() as $row){
$data[] = $row;
}
return $data;
}

}

and this is my view

<?php foreach($clientes as $hotels) :?>
<div class="content-hotels">
<a class="titulo_hotel">DestinoUrl; ?&gt;_hotels/&lt;?php echo $hotels->Url; ?&gt;">&lt;?php echo $hotels->Name; ?&gt;</a>
<p class="content-destinos"> &lt;?php echo $hotels->Destino; ?&gt; (&lt;?php echo $hotels->Locacion; ?&gt</p>
<p class="contenido">&lt;?php echo $hotels->SmallContent; ?&gt;</p>
<div class="links">
<a class="small" href="#">More</a>
<a class="small" href="#">Rooms</a>
<a class="small" href="#">Map</a>
<a class="small">Url; ?&gt;">Comments</a>
</div>
<p class="content-reviews">Guest Rating: 5 / 10 |</p><a href="#">(56) Guest Reviews &raquo;</a>

<p class="comment-share">Have you been to this hotel ?</p><a href="#">Share your comments here.</a>
</div>
&lt;?php endforeach; ?&gt;

In the class content-reviews in which I put 56 I would like to count the comments for each hotel can someone help me please

[eluser]mddd[/eluser]
You need to GROUP BY the relevant id. That is the hotel id, or the hotel data id, depending on how your tables work exactly. Anyway, do the grouping on the thing that identifies the group of comments you want to count. Then use COUNT to count the comments per group.

A simplied version: if you had only a table of hotels and a table of comments:

Code:

SELECT hotels.*, COUNT(comments.id) AS num_comments
FROM hotels
JOIN comments on comments.hotel_id = hotels.id