Hi, I'm working on a mysql-based survey program. On the user page I display the survey form, and on the admin page I display the results for each question. The problem, which I find puzzling, is that on the results page the order of the questions (sorted by specified question numbers) are slightly jumbled, while on the user form they work fine. I've looked at these mysql queries for a while but can't seem to pinpoint the problem. I'll paste them below and if you notice anything out of the ordinary please let me know.
*Edit: I'm posting these as plain text - didn't format well in the [code] tags...
Here's the one for the userform, which works fine:
SELECT questions.quest ion_type_id,que stions.question _id,questions.q uestion_text,qu estions.show_co mment,question_ choice_group.qu estion_group_ar ray from questions LEFT JOIN question_choice ON (questions.ques tion_id = question_choice .question_id) AND (question_choic e.survey_id = '$survey_id') LEFT JOIN question_choice _group USING (question_choic e_group_id) WHERE questions.surve y_id = '$survey_id' ORDER BY questions.quest ion_order_num
Here's the one for the results page, whose order doesn't quite work:
SELECT questions.surve y_id,questions. question_id,que stions.question _text,answers.a nswer_text,ques tions.question_ type_id,questio n_choice.questi on_choice_group _id,question_ch oice_group.ques tion_group_arra y,
count(answers.a nswer_text) as 'num_answers',q uestions.questi on_order_num
FROM questions LEFT JOIN answers ON (questions.ques tion_id = answers.questio n_id AND questions.surve y_id = answers.survey_ id)
LEFT JOIN question_choice ON (questions.ques tion_id = question_choice .question_id AND questions.surve y_id=question_c hoice.survey_id )
LEFT JOIN question_choice _group ON question_choice .question_choic e_group_id = question_choice _group.question _choice_group_i d
WHERE questions.surve y_id='$survey_i d'
GROUP BY questions.quest ion_text,answer s.answer_text
ORDER BY questions.quest ion_order_num
*Edit: I'm posting these as plain text - didn't format well in the [code] tags...
Here's the one for the userform, which works fine:
SELECT questions.quest ion_type_id,que stions.question _id,questions.q uestion_text,qu estions.show_co mment,question_ choice_group.qu estion_group_ar ray from questions LEFT JOIN question_choice ON (questions.ques tion_id = question_choice .question_id) AND (question_choic e.survey_id = '$survey_id') LEFT JOIN question_choice _group USING (question_choic e_group_id) WHERE questions.surve y_id = '$survey_id' ORDER BY questions.quest ion_order_num
Here's the one for the results page, whose order doesn't quite work:
SELECT questions.surve y_id,questions. question_id,que stions.question _text,answers.a nswer_text,ques tions.question_ type_id,questio n_choice.questi on_choice_group _id,question_ch oice_group.ques tion_group_arra y,
count(answers.a nswer_text) as 'num_answers',q uestions.questi on_order_num
FROM questions LEFT JOIN answers ON (questions.ques tion_id = answers.questio n_id AND questions.surve y_id = answers.survey_ id)
LEFT JOIN question_choice ON (questions.ques tion_id = question_choice .question_id AND questions.surve y_id=question_c hoice.survey_id )
LEFT JOIN question_choice _group ON question_choice .question_choic e_group_id = question_choice _group.question _choice_group_i d
WHERE questions.surve y_id='$survey_i d'
GROUP BY questions.quest ion_text,answer s.answer_text
ORDER BY questions.quest ion_order_num