dear 'alternate_dph',
>Yes, I'm using a join statement as a precaution to make sure all my
>questions have an associated answer, but still I'm using distinct to
>eliminate duplicates. Anyways, here's the query that's giving me
>duplicates:
>
>$random_questions_query = "SELECT DISTINCT tgs_questions.q_id
>FROM tgs_questions, tgs_quest_ans
>WHERE tgs_quest_ans.q_id = tgs_questions.q_id
>ORDER BY RAND() LIMIT 0, 5";
You are using multiple choice or open questions?
In case of multiple choice, you will not be able to use the join.
Imagine two tables:
Questions (qu_id:smallint; qu_question: text)
Answers (an_id: smallint; an_qu_id: smallint; qu_answer: text)
when you make a join and then pull random rows you will not be able to
guarantuee you pulled ALL the answers belonging to the question.
select * from questions left join answers on qu_id=an_qu_id order by rand()
limit 0,5
will pull five rows, but may be one answer each of five completely different
questions!
Let's say you have 10 questions with each four answers defined; the join
result would be 40 rows. Pulling out eight of these to get two complete
question/answer sets using rand() is not going to work. The chance to have
even only one complete set is marginal!
[note how a logical choice of primary/foreign key names eliminates the need
to prepend with the table name]
Thus your query needs to be:
select qu_id from questions order by rand() limit 0,5.
You take the results and then iterate: take the qu_id and make a query like
select * from answers where an_qu_id
By the way, Riquez...
>I'm wondering why you would keep the questions & answers in separate
>tables?
Because questions might be open answer, and n-answer multiple choice, where
n is not fixed.
If you fix n, you might get away with putting n columns with answers in the
questions table but it is not nice and you will not be able to add a
question with 6 answeroptions once you set up the table with only 5
answercolumns (fields)
if you define questions as
questions:
qu_id: smallint
qu_question: text
qu_answers: smallint
then qu_answers = 1 will be an open question, others multiple choice, and
you can vary by any way you like the type of question and, if multiple
choice, the amount of possible answers without ever having to mess with the
table structures again.
An generally: the more flexible the better, as the customer later on
**always** has a wish that does not fit in a rigid table design. And it is
not much more work anyway. When implmenting the tables it costs maybe 2
minutes more and will save you hours and headache later on.
Marc
___________________________________
Webmail TOP Internet www.top.com.br
Community email addresses:
Post message: [email protected]
Subscribe: [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
List owner: [EMAIL PROTECTED]
Shortcut URL to this page:
http://groups.yahoo.com/group/php-list
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-list/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/php-list/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
