如何计算相关表中的行数

I have two tables in my database

articles With the following fields

id author post_date modification title section_id views featured content post_status

sections With the following fields

  • sec_id description slug category

I have a function in my model to return all the sections

function get_all() {
            $q = $this->db->select('sec_id,category,slug,description')
                            ->from('sections')
                        ->order_by('category','asc');
            $ret['rows'] = $q->get()->result();
            return $ret;
        }

Am display this in a table

<div id="categories">

<div id="toolbar">
</div>

<table>
<thead class="table_header">
<tr>
<th><input type="checkbox" name="check_all" id="master_box" onclick="toggle(this)" /></th>
<?php foreach($fields as $field_name => $field_display):?>
<th><?php echo $field_display ?></th>
<?php endforeach; ?>
</tr>
</thead>

<tfoot class="table_header">
<tr>
<th><input type="checkbox" name="check_all" id="master_box" onclick="toggle(this)" /></th>
<?php foreach($fields as $field_name => $field_display):?>
<th><?php echo $field_display ?></th>
<?php endforeach; ?>
</tr>
</tfoot>

<tbody>
<?php foreach($categories as $cat):?>
<tr>
<td><input id="sec_id"name="id[]" type="checkbox" value="<?php echo $cat->sec_id; ?>" /></td>
<td><?php echo $cat->category; ?></td>
<td><?php echo $cat->description; ?></td>
<td><?php echo $cat->slug; ?></td>
<td></td>
</tr>
<?php endforeach;?>
</tbody>

</table>

</div>

If you notice there is an empty tag.It is meant to display the number of articles in the category(section).

How do get the number of articles associated with each category?

1个回答



  SELECT s.sec_id,s.category,s.slug,s.description,COUNT(a.id)FROM sections  s 
LEFT JOIN articale a ON s.sec_id = a.section_id
WHERE 1
GROUP by s.sec_id
ORDER BY s.category ASC
</ code> </ pre>
</ div>

展开原文

原文

SELECT s.sec_id,s.category,s.slug,s.description,COUNT(a.id) FROM sections s 
    LEFT JOIN articale a ON s.sec_id = a.section_id 
WHERE 1 
GROUP BY s.sec_id 
ORDER BY s.category ASC 

douzhu3654
douzhu3654 谢谢它工作,我只是修改计数(a.id)计数(a.id)作为文章
9 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问