storing multiple answers from 1 question

cmoore67

(mySQL PHP)
How do I store data from a question that may have more than one answer. Do I create a field for each answer or do I add each answer to the same field? If the latter, how do I query that field? (Note: I have about 50 possible answers to the question.)

for example:
imagine a form that asks what type of recreation is preferred,

name [ Chris ]

Choose all that apply:
[x] biking
[x] skiing
[ ] running

would the table look like this:
id : name : bike : ski : run
1 : chris: 1 : 1 : 0

or like this:
id : name : interests
1 : chris: bike ski

If it is the latter what does my WHERE query look like if I want to find everyone who likes to ski and bike.

Thanks,
Chris

Anon

For the later, you can use MySQL's FULLTEXT search function. See MySQL manual on their website. That function does wonderfull search, including % of relevance, etc etc ...

The bad news is that the minimum word length is 4 letters. If you have 3 letter word, then you need to tweak MySQL and recompile.

Hope this helps.

MSN

kparker

would the table look like this:
...
or like this:

Neither, because a single table is not the way to do this.
Instead, you need 3:

create table person
   {
   id int auto_increment primary key,
   name text
   };

create table interest
   {
   id int auto_increment primary key
   name text
   };

create table person_interest
   {
   person_id, / references person.id /
   interest_id, / references interest.id /
   primary key (person_id, interest_id)
   }

Getting the data back out is harder in mysql than it should be, however, due to the lack of subqueries. Ideally (and in any fully-compliant sql dbms) you can say:

select person.id, person.name from person,person_interest where person.id = person_interest.person_id and person_interest.interest_id = (select id from interest where name = 'php programming');

However, in real life, you may already know the value of 'interest.id' for a particular query, and thus can skip it. (If not, the mysql way is to just perform the 2 queries in sequence. This is not too bad in a case like my example, where the subquery is going to return a single row only.)

}
id : name : bike : ski : run
1 : chris: 1 : 1 : 0

                      or like this: 
                      id : name : interests 
                      1 : chris: bike ski