Alex,
With reference to Dave's comments/suggestions:-
> > I've got a 700-question multiple choice quiz that I've got imported into
> > MySQL, with one small problem: the answers to the quiz are not a separate
> > column. I have three columns for each of the possible answers; the correct
> > answer is denoted by a "*" in the appropriate field. For example, if I have
> > columns A, B, and C for the three answers, they would contain data such as
> > "George Washington", "*Abraham Lincoln", and "Bill Clinton".
> >
> > I'd like to be able to select out all the fields with a * in them, and then
> > place them in a new, blank column called Answer. However, I'm really not
> > sure how to select these out, since I can't say "where <colname> like "*%".
> > Further complicating the matter is the fact that, in a few of the questions,
> > there are multiple answers, and thus multiple columns with the asterisk in
> > them.
> >
> > How would I go about selecting out these fields? Even if I can only get
> > those with one answer, I'd be immensley appreciative.
>
> _If_ you can re-import I'd _strongly_ suggest you change your schema.
>
> You'd have one table for questions:
>
> Question
> ------------
> question_id int PK
> question text
>
> Answer
> ------------
> question_id int PK (part 1)
> display_order int PK (part 2)
> answer text
> is_correct tinyint (or bool, or whatever)
>
>
> Then it would _really_ easy to find all the correct answers, no matter how
> many you had.
>
> Ok, that's not necessarily the answer you were looking for, but if you did
> the import once maybe you can do it again. And if not at least its
> something to think about next time.
in agreeing with the observation that the data design does not seem 'right', I offer
an alternative solution...
Let's start with the simple case: one question, four multi-choice alternatives
("answers"), and one correct
response. In this case, the above schema would have four rows in the Answer table and
thus only one is_correct
field containing 'useful' data with three other is_correct fields that are
superfluous. (in this restricted
case) If there is only ONE correct response for EACH question - is_correct(_answer) is
related to question, not
to alternative response ("answers")! So it would be more storage-efficient to put the
is_correct field (suitably
renamed) in the Question table.
Moving on...you did mention that "in a few of the questions, there are multiple
answers". Adding to the above
structure would quickly become a little 'messy', and if you were to have multiple
'is_correct' columns the
Question (or Answer) table would become de-normalised and even less
storage-inefficient. However if it were
changed from a tinyint field to ENUM (or if you prefer - and many people find them
easier, a SET) column -
depending upon how you want to phrase your query transactions, it might then work in a
way that suits.
Any use?
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
