Hi all

I am Didi, yes I am a gal.

I am a final year college student, doing my project and I am having some problems with mysql and php. Ok this is my scenario.

I am doing and online store for a health company,first a visitor takes a
nutritional needs assessment where he answers several questions about him self, like the conditions that he has at the moment.What I have is an
array of conditions with 300 conditions in all.This can increase as time goes on.

So the html we have something like this


<input type="checkbox" name="conditions[]" value="Bad Circulation">
<input type="checkbox" name="conditions[]" value="Bad Breath">
<input type="checkbox" name="conditions[]" value="diabetes">
<input type="checkbox" name="conditions[]" value="asthma">
and so on....reaching 300 for noe


Now here is my problem,if a visitor lets say ticks 15 of these boxes how to we store all 15 in a database.

A couple of option I have is to use the explode/implode of php, but I was told that this is not a normalised database principle and my college Professor will definately penalise me big time.

I am sure what I am looking for is quite simple but I somehow seem to be looking in the wrong place, cause I mean a visitor to another site that sells books could buy 10 books, now I am confused as to how do we store it in a
table, that is normalised off-course.

Please help

Thanks everyone.
Didi

yes, you will have to "unstring" the value that is submitted with the form, which will be received by your script as a single comma-delimited string of values

then you have to assign each value to one row to a one-to-many table, which has two keys -- the person's id, and the condition

you could use the condition name as passed by the form, but the best way to do this is to have another table

each of those conditions should be a represented in a row in a Conditions table, with a primary key and a name

thus the one-to-many table is actually a many-to-many table

persons
23 tom
24 dick
25 harry

personconditions
24 103
24 106
24 108

conditions
102 Bad Circulation
103 Bad Breath
104 Diabetes
105 Asthma
106 No Heartbeat
107 Cooties
108 Acne

Thanks for the reply, I appreciate it, will that not mean that table below will not be normalised , not sure but I appreciate the advice.

personconditions
24 103
24 106
24 108

Thanks
Didi

personconditions is normalized

Dear Rudy

I appreciate the reply.

My problem is this, a visitor can take a nutritioin test now, but if he comes back again in 3 months and does a test, and he selects some of the same
conditions,will it then not have repeating values and a unnormalised
database

Please help

Didi

then the test itself would have to be an entity in your database, with a test table, and then the test results would have a foreign key to the test table, thus the same person could take multiple tests

Thanks again and I appreciate the reply.I can kind of picture what you are saying with the little knowledge I have,

Could you give me examples of the fields that might be in the TEST table
and the fields that might be in the TEST RESULTS table.

I am confused between this two.

Please help
Didi

tests
testid PK
testname
testdate

testresults
testid FK
personid FK

Dear Rudy

You so helpful,I looked at your website,very nice.

If I create the fields that you mentioned where would I store the actual persons conditions.This is my biggest problem,

Another problem that is common to the one above is the one below
lets say I have a medication table with the following fields

medication_id
medication_name
nutrients_depleted
products_to_take

Now the nutrients_depleted could be Vit A or Vit B but then again you can
have another medication that will deplete Vit A,Vit B and Zinc and that can even go up to 20 nutrients.

Same problem with products_to_take, that could be up to 10 different products.


So I am having problems with the storing of multiple values.

Please help
Didi

didi, thanks very much for the kind words

unfortunately i think it's time for you to try to come up with the solutions yourself

if you post something that shows a good amount of thinking behind it, you will find that we will reply and give suggestions for how to improve or cautions about what might cause problems

but we generally frown on people asking us to do their homework assignments

if you are having "problems with the storing of multiple values" then you need to go back to your course material and do some research on many-to-many relationships

sorry, but i shall have to decline to help you with your medication depletion problem, because i have already shown you a many-to-many relationship once

Dear Rudy

Sorry if I made it seem that way, actually have been trying and it was not working out,I have actually come up with something.I think your many to many relationships above have assisted as well.

This is what I have:-

Medication
medication_id PK
medication_name

Nutrient
nutrient_id PK
nutrient_name

Depletion
medication_id FK
nutrient_id FK


My problem is that my text book says a many to many relationship is a
symbol of poor database design cause a medication can be depleted by many
nutrients and one nutrient can be a cause for depletion in many medications, causing according to me knowledge a many to many relationship.So I have to (according to my text book) try to make two one-to-many relationships with many-to-many relationships.

Any way , I just want to say thanks for all the help, if you donot reply to me
now , I understand.


Take care
Didi

that is totally correct, you always implement a many-to-many logical relationship (which is very common and perfectly valid, and almost every database has a many-to-many logical relationship in it somewhere), as two physical one-to-many relationships with a common table

if your textbook did not emphasize the difference between a logical design and a physical design and how to transform the former into the latter, then it is a poor textbook

Thanks Rudy

I think I will get it right in time.

Once again, thanks for all the help

Take care
Didi