On Mon, 17 Dec 2001, Alex Kirk wrote: > 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. -dave /*================== www.urth.org We await the New Sun ==================*/ --------------------------------------------------------------------- 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
