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
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)
id=5Other 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 !!!