Welcome Guest, Not a member yet? Register   Sign In
Codeigniter Unable to join the table properly
#1

Hi I have 4 Tables.

1. t_category
  Id         category        parent_category     status
+------+----------------------+-----------------+-----------+
|   1    | Ayurvadic              |         0          |  Active    |
|   2    | Non Prescription     |         0          |  Active    |
|   3    | Pet Medicine          |         0          |  Deactive |
|   4    | Prescription Meds   |         0             Active     |
|   5    | Health Care Device |         0          |  Active     |
|   10  | Best Seller             |         0          |  Active     |
+------+----------------------+-----------------+-----------+
Mainly this is the top layer of the table, here the above 
categories are fixed.
One category may have any number of product(s).
Id is autogenerated which is unique for any category. 


2. t_product
  Id     product     generic_name   description  status
+------+-------------------------+-------------+----------+
|   1  | Alamia      | Alamia       |  desc1      | Active   |
|   2  | Bokachi     | Bokachi      |  desc2      | Active   |
|   3  | Bargol       | Bargol       |  desc3      | Deactive |
|   4  | Centrapin   | Centrapin   |  desc4      | Active   |
|   5  | Callis        | Callis         |  desc5      | Active   |
|   6  | Altroxin     | Altroxin      |  desc6      | Active   |
+------+-----------+-------------+-------------+----------+
We put the products in this table with the help of unique id
we can identify a particular product (Bargo-3) etc.
Id is autogenerated which is unique for any product. 

3. t_cp_link
  Id     category_id product_id    
+------+------------------------+
|   1  |     3     |     1      |
|   2  |     3     |     2      |
|   3  |     3     |     3      |
|   4  |     4     |     5      |
|   5  |     4     |     6      |
|   6  |     1     |     4      |
+------+-----------+------------+
category_id and product_id come from t_product_pack table at the time we enter a record in t_product_pack.



4. t_product_pack
  Id     product_id  Category_id  descr.    status     Dosage  unit_price Selling_price   Stock
+------+-------------------------+--------+----------+--------+----------+--------------+-------+
|   1    |   1          |     3         |  desc1 | Active     |  10     |  1250    |   125         | Active|
|   2    |   2          |     3         |  desc2 | Active     |   4      |  2550    |   400         | Active|
|   3    |   3          |     3         |  desc3 | Deactive |   5      |  1000    |   500         | Active|
|   4    |   5          |     4         |  desc4 | Active     |   6     |   5900    |  1250        | Active|
|   5    |   6          |     4         |  desc5 | Active     |   5     |   2200    |  580         | Active|
|   10  |   4          |      1         |  desc6 | Active    |   5      |  16.50   |  16.00       | Active|
+------+-----------+-------------+--------+----------+--------+----------+--------------+-------+

When we enter the detail of a particular in the product package we insert the category_id(id of t_category foreign key)
and product_id (from t_product id become a foregin key here).

When we save a record here we put the category_id and product_id in t_cp_link for linking the product table with category table.

Now the resullt i want like this.

Category_id   Category_name      Product_id  Product   Dosage  unit_price Selling_price   Stock
+-----------+------------------+-----------+--------+---------+----------+-------------+-----------+
|     3     | Pet Medicine        |     1      | Alamia   |   10    |  1250    |  125        |  Active     |
|     3     | Pet Medicine        |     2      | Bokachi |    4     |  2550    |  400        |  Active     |
|     3     | Pet Medicine        |     3      | Bargol   |    5     |  1000    |  500        |  Active     |
|     4     | Prescription Meds|     5      | Callis     |     6    |   5900    |  1250       |  Active    |
|     4     | Prescription Meds|     6      | Altroxi   |     5    |   2200    |  580        |  Active     |
|     2     | Ayurvadic            |     4       | Callis    |     5     |  16.50   |  16.00      |  Active     |
+-----------+------------------+-----------+--------+---------+----------+-------------+-----------+

by using the above 4 or less no of tables.

I write the following query:
but it does not display the category_id

    public function get_all_package() {

$this->db->select('t_product_pack.*,t_product.*');
        $this->db->FROM('t_product_pack');
$this->db->join('t_product', 't_product_pack.product_id = t_product.id','left'); 
        $this->db->order_by("product_id", "ASC");  
        $query_result = $this->db->get();
        $result = $query_result->result();
        echo "<pre>";
        print_r($result);
        exit();
        return $result;
    }

I want it to order by the id of t_product_pack table.

Please help.


Thank you very much.
Reply
#2

In short, what is really the problem?
Reply
#3

(This post was last modified: 02-23-2017, 08:28 PM by AzrielOmega.)

First, if one product belong only for one category, you don't need t_cp_link table. Just add CategoryID column to Product table
You don't need CategoryID in Product Pack table either, if the ProductID is unique already.

Now you will have 3 table: Category, Product and ProductPack. the Query should be like this:

$this -> db -> select('Category.CategoryID, CategoryName, Product.ProductID, Product.ProductName, Pack.Dosage, Pack.Unit_price, Pack.Selling_price, Pack.Stock');
$this -> db -> from('ProductPack as Pack');
$this->db->join('Product', 'Product.ProductID = Pack.ProductID', 'left');
$this->db->join('Category', 'Category.CategoryID = Product.CategoryID', 'left'); 
$this->db->order_by("Product.ProductID", "ASC");  
$query = $this->db->get();
if ($query -> num_rows() > 0) {
    return $query -> result_array(); // or result();
} else {
    return false;
}
Reply




Theme © iAndrew 2016 - Forum software by © MyBB