user7047368 user7047368 - 11 days ago 5
MySQL Question

counting the blog ids and displaying the results in descending order in mysql codeigniter

Counting the no of blog ids(same) and displaying the results in descending order.I am having recommended read section here i need to display the blogs based on the count of no of categories for a particular blog.My table looks like this
blogs

blog_id| image_path | description
-------------------------------------------
1 | image.png | description
2 | image1.png | description
3 | image2.png | description
4 | image3.png | description


blog_categories

blog_category_id | blog_id | category_id
-------------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 3
4 | 3 | 4
5 | 3 | 2
6 | 3 | 6


Here in blog_categories table blog_id 3 count is 3 and for 1 the count is 2 so while displaying the the results the first one should be

blog_id
3
1
2


It should the result in this format.But i am getting only one record from the query

Here is my code:

Controller:

public function article()
{
$this->load->model('blogs_model');
$data['records4'] = $this->blogs_model->get_all_recommended();
$data['mainpage']='blogs';
$this->load->view('templates/templatess',$data);
}


Model:

function get_all_recommended()
{
$this->db->select('count(*),image_path,description');
$this->db->from('blog_categories');
$this->db->join('blogs AS B','B.blog_id=blog_categories.blog_id','INNER');
$this->db->order_by("blog_categories.blog_id", "DESC");
$this->db->limit('4,4');
$query = $this->db->get();
if($query->num_rows()>0)
{
return $query->result();
}
else
{
return false;
}
}


View:

<?php if(isset($records4) && is_array($records4)):?>
<?php foreach ($records4 as $r):?>
<div class="clearfix float-my-children">
<img src="<?php echo base_url();?>admin/images/blogimages/thumbs/<?php echo $r->image_path;?>" width=100>
<div class="blogclasstext134"><?php echo $r->blog_text;?></div>
</div>



Answer
function get_all_recommended()
{ 
    $this->db->select('B.blog_id,count(*),image_path,blog_title');
    $this->db->from('blog_categories');
    $this->db->join('blogs AS B','B.blog_id=blog_categories.blog_id','INNER');
    $this->db->join('categories AS C','C.category_id=blog_categories.category_id','INNER');
    $this->db->group_by('B.blog_id');
    $this->db->order_by("count(blog_categories.blog_id)", "DESC");      
    $this->db->limit('4,4');
    $query = $this->db->get();
    if($query->num_rows()>0)
    { 
        return $query->result();
    } 
    else
    {
        return false;
    } 
}

Correct answer for counting the blog_ids and displaying the results.