well, I'm thinking I may just redo the table design to make it more
complex to solve the rand problem in a way I feel safe.
CREATE TABLE tgs_questions
(q_id INT(3) UNSIGNED PRIMARY KEY,
question VARCHAR(140));
CREATE TABLE tgs_quest_set
(qs_id INT(3) UNSIGNED PRIMARY KEY,
q_id INT(3) UNSIGNED NOT NULL REFERENCES tgs_questions (q_id),
set_id INT(3) UNSIGNED NOT NULL REFERENCES tgs_set_answers (set_id),
c_ans_id INT(3) UNSIGNED NOT NULL REFERENCES tgs_answers (ans_id));
CREATE TABLE tgs_set_answers
(qs_id INT(3) UNSIGNED PRIMARY KEY,
set_id INT(3) UNSIGNED NOT NULL,
ans_id INT(3) UNSIGNED NOT NULL REFERENCES tgs_answers (ans_id));
CREATE TABLE tgs_answers
(ans_id INT(3) UNSIGNED PRIMARY KEY,
answer VARCHAR(40));
Then change the random query to be the following:
$random_questions_query = "SELECT DISTINCT q_id, set_id
FROM tgs_quest_set
ORDER BY RAND() LIMIT 0, 5";
That way I can feel safe about every selected question having a set of
answers associated with it and not get duplicate rows.
And, no, I'm using MySQL v3.23.58 and I've been told getting it
updated will not happen anytime soon so I can't use either subqueries
or views.
--- In [email protected], "Mike Franks" <[EMAIL PROTECTED]> wrote:
>
> I'm no longer clear on who submitted the original question.
>
> This may not be accurate syntax, and I've never used RAND(), but I
> suspect it would solve the problem (at least as I understand the
> problem). Should work in MySQL 4 and above.
>
> select tgs_questions.q_id
> from tgs_questions, tgs_quest_ans
> where tqs_quest_ans.q_id = tqs_questions.q_id
> and tqs_questions.q_id in
> (select q_id from tqs_questsions order by rand() limit 0, 5);
>
> Mike
>
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/
