How to display products by category using codeigniter

Question

I have a database with two tables with category and product names. I want to display products by category.

how

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

But my code returns the category name with each product. how

 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 request using the group_concat and group by group_concat and you will get comma separated values ​​that you can just 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 verified

Answer 2

You need to create an auxiliary array of products with a category as the corresponding key.

 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); $products = array(); if ($query->num_rows()) { foreach ($query->result_array() as $row) { $products[$row['c_name']][] = $row; } } return $query->result_array(); } 

And in sight:

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

Answer 3

The model should be:

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.id"; $query = $this->db->query($sql); return $query->result_array(); }