CODEWITHSUNDEEP
phpmysqlcodeigniter
User1019
User1019

Reputation: 51

How to display products by categories using codeigniter

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; ?>

Upvotes: 5

Views: 7155

Answers (3)

Vikas Burman
Vikas Burman

Reputation: 355

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();    
}

Upvotes: 0

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21437

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

Upvotes: 1

Pupil
Pupil

Reputation: 23948

You need to create sub array of products with category as their respective 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 view:

<?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; ?>

Upvotes: 0

Related Questions