phpcodeigniterjoinselectquery-builder

CodeIgniter query builder SELECT query with JOIN clause

This is my categories. If I click on a category then it goes to its view page, and show all products related with this selected category. enter image description here

I am trying like this, but it is not working. I am using 3 tables for this situation.

1.categoriesenter image description herecategories table

2.products enter image description here 3.product_cat

enter image description here

Controller:

<?php 
class Clothing extends Controller{
    
    function product_details(){
        $id=$this->uri->segment(3);
        
        $this->load->model('Products_model');
        $data['products']=$this->Products_model->product_details($id);
        
        //$this->load->view('clothe',$data);
    }
}

?>

Model:

<?php
class Products_model extends Model {
    function product_details($id){
                    $query=$this->db->select("*")
                    ->from("product_cat")
                    ->where("categories_id",$id);
                    return $query;
        }
    }

View:

<html>
    <head></head>
    <body>
        
        <?php  foreach ($products as $v_menu) { ?>
                    <?php echo $v_menu; ?>
        <?php } ?>
    </body>
</html>
Solution

  • You can join the two tables products and product_cat as below so that you can get the product details too-

    return $query=$this->db->select('*')
        ->from('product a')
        ->from('product_cat p')
        ->where("p.product_id = a.id")
        ->where('p.categories_id =', $id)
        ->get()->result();