Codeigniter Unable to join the table properly |
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.
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; } |
Welcome Guest, Not a member yet? Register Sign In |