How to fixed array problem in Codeigniter framework?

Question

I am a beginner of CodeIgniter. I have some issues in arrays. I have 3 dropdown menus to select a medicine that is fetched through the medicine database. but I don't understand how to put 3 values in one field of the table. there are two tables:

Medicine table(medicine_id ,medicine_name)
Prescription table(prescription_id,patient_id,symptoms,disgonis,medicine_id,m_note,date)

How to put multiple values in a medicine_id column?

Don't do that, you will only end in trouble. Have a look at database normalization first — Nico Haase, Jun 18 '19 at 07:38
What do you mean by that? Have you looked up what database normalization is, and why that helps you to get a good database scheme? — Nico Haase, Jun 18 '19 at 07:42
Please refer to [this answer](https://stackoverflow.com/a/3653574/8566549) so you could decide better on what's the best data storing option for you — Hasta Dhana, Jun 18 '19 at 10:40

score 2 · Answer 1 · answered Jun 18 '19 at 11:11

you cant put multiple values into one column in mysql. You got a so called 1:n relation here. 1 Prescription can have n medicines.

For representing that relation in mysql you need to create a new table

Prescription_Medicine(prescription_id,medicine_id)

where you have one row for every medicine thats on the prescription. You should get a quick overview over database normalization as the commenters suggested.

score 2 · Answer 2 · answered Jun 18 '19 at 11:25

First Approach: Not Recommended

You can set the medicine_id field as varchar and save all medicine id in comma separated form. When you need to join Prescription table to Medicine table you have to user FIND_IN_SET. But it is not recommended because it is against normalization rules. and it will slow down your execution.

Second Approach:

You can make another table, where you will store prescription_id and medicine_id. As @Sam Tigle told you.

score 1 · Answer 3 · answered Jun 24 '19 at 13:26

You can save an array into a database field in different ways

Serialized form
Comma-Separated string
JSON (Read more about JSON data type here)

But it is not the proper solution for your problem. You should consider a workaround as given below:

Usually, a single prescription can have multiple medicines. Therefore a One to Many relation is there.

It is recommended that you create two tables as prescriptions and prescription_medicines, and save the prescription details in the prescriptions table and then add each medicine in a prescription as a row in the prescription_medicines table.

The table structure can be something as shown below.

prescriptions table

id (primary_key), doctor_id, patient_i`, date, .....

prescription_medicines table

id, prescription_id, medicine_id, dosage, ...

Here the prescription_id is the primary_key of prescriptions_table and the medicine_id should be a primary_key of medicines_table

Now we can relate the prescriptions with medicines table.

EXAMPLE QUERY: To get the prescribed medicines and its dosage for a patient having patient id 123

SELECT med.name, pres_med.dosage 
FROM prescriptions AS pres
JOIN prescription_medicines AS pres_med ON pres_med.prescription_id = pres.id
JOIN medicines AS med ON med.id = pres_med.medicine_id
WHERE pres.patient_id = 123;
-- ADD YOUR OWN LOGIC TO GET THE LASTEST/RELEVANT PRESCRIPTION

How to fixed array problem in Codeigniter framework?

3 Answers3