Join query with comma separated values in codeigniter

Question

I have a table structure like

Table Name : crm_mrdetails

     id | mr_name | me_email     | mr_mobile  | mr_doctor|
     -------------------------------------------------
     1  | John    |abc@gmail.com | 9876543210 | 1,2      |

    Table Name : crm_mr_doctor

    id | dr_name     | specialization| product |
    -------------------------------------------- 
    1  | Abhishek    | cordiologist  | 1,2     |
    2  | Krishnan    | Physician     |2,4,5    |

    Table Name: crm_product

    id | product_name | unit| pts |
    -------------------------------- 
   1  | Crocin       | 50  | 50  | 
   2  | Crocin2      | 60  | 100 | 
   3  | Crocin3      | 30  | 90  | 
   4  | Crocin4      | 70  | 80  | 
   5  | Crocin5      | 80  | 50  |

I need to get data from "crm_mrdetails" table and join table "crm_mr_doctor" and "crm_product" ,joining condition like

crm_mrdetails.mr_doctor = crm_mr_doctor.id 
crm_mr_doctor.product = crm_product.id

But there is a comma separated values in column and i need output in below manner.

Output:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [mr_name] => john
            [mr_email] => abc@gmail.com
            [mr_mobile] => 9876543210
            [doctor] =>([0]=>stdClass Object
                           (
                             [id] => 1
                             [dr_name] => Abhishek
                             [product] =>([0]=>stdClass Object
                                              (
                                                [id] => 1
                                                [product_name] => crocin
                                                [unit] => 50
                                                [pts] => 50
                                              )
                                           [1]=>stdClass Object
                                              (
                                                [id] => 2
                                                [product_name] => crocin2
                                                [unit] => 60
                                                [pts] => 100
                                              ) 
                                         )

                            )
                         [1]=>stdClass Object
                          (
                             [id] => 1
                             [dr_name] => Krishnan
                             [product] =>([0]=>stdClass Object
                                              (
                                                [id] => 2
                                                [product_name] => crocin2
                                                [unit] => 60
                                                [pts] => 100
                                              )
                                           [1]=>stdClass Object
                                              (
                                                [id] => 2
                                                [product_name] => crocin4
                                                [unit] => 70
                                                [pts] => 80
                                              ) 
                                           [2]=>stdClass Object
                                              (
                                                [id] => 2
                                                [product_name] => crocin5
                                                [unit] => 80
                                                [pts] => 50
                                              ) 
                                         )

                            )
                       )
         )
)

I need to write query in codeigniter to get output like above Please help me to achieve this result, Thanks

Answer 1

PROBLEM

As @Liam Sorsby and @blckbird mentioned in there comments you should use normalization rules. Using comma separated values is not a good way and your tables are going to be more complex to manage when the size of data increases.

Just imagine "John" (with id=1) has got many doctors so your table would be something like this :

id | mr_name | me_email     | mr_mobile  | mr_doctor              |
 -----------------------------------------------------------------+
 1  | John    |abc@gmail.com | 9876543210 | 1,2,5,6,8,9,10,11,78  |

Now Imagine that you want to remove relation between doctor with id=5 and Jhon (user with id=1)

1. You will have to get list of all the doctors who are related to Jhon
2. Explode the result
3. Search for id=5
4. Remove it
5. Implode the result
6. Save it to table.

Other operations like searching if a doctor and User have a relationship, adding new relationships and removing existing relationships becomes more complex using this architecture.

---

BEST SOLUTION:

Looking at the current scenario it seems like you have a many-to-many Relationship between a user and a doctor i.e "A doctor can have many Patients and a Patient can belong to many doctors"

In such case you should be using a "pivot table" to maintain relationship between two tables. In your case it would be something like this:

Table Name: crm_mrdetails

id | mr_name | me_email     | mr_mobile    |
 ------------------------------------------+
 1  | John    |abc@gmail.com | 9876543210  |

Table Name : crm_mr_doctor

id | dr_name     | specialization  |
-----------------------------------+
1  | Abhishek    | cordiologist    |
2  | Krishnan    | Physician       |

Now you will have a Pivot table to maintain the relationship between the two tables :

Table Name : crm_doctor_mr

id | dr_id     | mr_id  |
---------------+--------+
1  |  1        |   1    |
2  |  2        |   1    |

Same thing will be done for the Products and doctor's relationship.

products and doctors too have a many-to-many relationship- "A product belongs to many doctors and a doctor has many products."

so you will have a products table like so :

Table Name: crm_product

   id | product_name | unit| pts |
   ---+--------------+-----+-----+ 
   1  | Crocin       | 50  | 50  | 
   2  | Crocin2      | 60  | 100 | 
   3  | Crocin3      | 30  | 90  | 
   4  | Crocin4      | 70  | 80  | 
   5  | Crocin5      | 80  | 50  |

And a pivot table to maintain the relationship :

Table Name : crm_doctors_products

id | dr_id      | product_id |
----------------+------------+
1  |    1       |   1        |
2  |    1       |   2        |
3  |    2       |   2        |
4  |    2       |   4        |
5  |    2       |   5        |

Once You have made changes in Your Database You can query any record using simple Joins.

---

ANOTHER SOLUTION

If you are at a stage where it is not possible to make changes to DB You can use this solution however i would prefer the one above.

public function get_mr_details(){
   $mrArray = $this->db->get('crm_mrdetails')->result();
   foreach($mrArray as &$mr){
     $mr['doctor'] = $this->db->where_in('id',$mr['mr_doctor'])->get('crm_mr_doctor')->result();
     foreach($mr['doctor'] as &$dr){
         $dr['product'] = $this->db->where_in('id',$dr['product'])->get('crm_product')->result();
     }
     unset($mr['mr_doctor']); 
   }
   return $mrArray;

}

However this solution will cause multiple queries to run on database and will exponentially effect performance with the increase in size of data in above tables.

Hope this helps !!!