How to display products by categories using codeigniter

Question

I have a database with two tables named categories and products. I want to display products by categories.

Like

Category 1
         -Product 1
         -Product 2 
Category 2
         -Product 1
         -Product 2

But, my code returns Category name with each product. Like

Category 1
         -Product 1
Category 1
         -Product 2
Category 2
         -Product 1
Category 2
         -Product 2

Here is my code attempt:

Model

function NestedProducts()
{
    $sql = "Select C.c_name, P.productname 
        From categories C left join products P on C.id = P.category_id 
        Order by C.c_name";
        $query = $this->db->query($sql);

        return $query->result_array();
}

Controller

public function index()
{
    $data['productsbycategory'] = $this->Model->NestedProducts();
    $this->load->view('home', $data);
}

View

<?php foreach($productsbycategory as $row) : ?>
   <div>
      <h4><?php echo $row['c_name']; ?></h4>
          <div>
          <h6><?php echo anchor('method/'.$id, $row['productname']); ?></h6>        
         </div>
   </div>
<?php endforeach; ?>

Answer 1

Just update your query using group_concat and group by clause and you will get comma separated values which you can simply explode on comma

$sql = "Select C.c_name, group_concat(P.productname) as productname 
        From categories C left join products P on C.id = P.category_id 
        group by C.c_name
        Order by C.c_name";

Note: Not Tested