Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Posts
    8

    Smile Unanswered: mysql and php help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    8
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    personconditions is normalized
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    8
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Posts
    8
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tests
    testid PK
    testname
    testdate

    testresults
    testid FK
    personid FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Posts
    8
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2005
    Posts
    8
    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
    Last edited by dd131313; 02-09-05 at 12:23.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2005
    Posts
    8
    Thanks Rudy

    I think I will get it right in time.

    Once again, thanks for all the help

    Take care
    Didi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •