I'm hoping some of the more experienced mysql-ers can help me with something.
I'm coding a php article for a quiz thing. I have two tables -- quiz_questions and quiz_answers -- which are set up like this:
quiz_questions:
questionID
quiz_number
question
answerA
answerB
answerC
answerD
answer_correct
explanation
sort
quiz_answers:
[counter]
quiz_number
ID_MEMBER
questionID
answer
(I know. I have quiz_number in both and it's redundant. It's just easier that way. :) )
When users take the quiz, if they leave a question blank, no record of that question is added to the answers table.
I want the users to be able to go back and see what their answers were, and what the right answers are. So I need a query that will list all of the questions for a given quiz, along with the logged-in user's responses to them. I tried
SELECT question, answerA, answerB, answerC, answerD, answer_correct, explanation, answer
FROM quiz_questions
LEFT JOIN quiz_answers ON quiz_answers.questionID = quiz_questions.questionID
WHERE ID_MEMBER = '$user_id'
AND quiz_questions.quiz_number = '$prev'
ORDER BY sort
But that just gives me the questions from that quiz that the user answered, instead of all the questions from the quiz. I could do this with two queries easily, but there's got to be a way to do it with one.
Maybe something like..
mysql_query("SELECT qq.question, qq.answerA, qq.answerB, qq.answerC, qq.answerD, qq.answer_correct, qq.explanation, qa.answer
FROM quiz_questions AS qq
LEFT JOIN quiz_answers AS qa ON qa.questionID = qq.questionID
WHERE qq.ID_MEMBER = '$user_id'
AND qq.quiz_number = '$prev'
ORDER BY qq.sort");
Bloc, I think your query is the same as mine, but just better-written. :)
I think I've got it, but this has got to be one of the most convoluted queries I've ever seen. The following query works when I use it in phpmyadmin, when I substitute numbers for the variables.
$query = db_query("(SELECT question, answerA, answerB, answerC, answerD, answer_correct, explanation, answer, sort
FROM quiz_questions
LEFT JOIN quiz_answers ON quiz_answers.questionID = quiz_questions.questionID
WHERE quiz_questions.quiz_number = '$prev'
AND ID_MEMBER = '$user_id')
UNION
(SELECT question, answerA, answerB, answerC, answerD, answer_correct, explanation, NULL , sort
FROM quiz_questions
WHERE quiz_questions.quiz_number = '$prev'
AND quiz_questions.questionID NOT IN
(SELECT questionID
FROM quiz_answers
WHERE quiz_answers.quiz_number = '$prev'
AND ID_MEMBER = '$user_id')
)
ORDER BY `sort`", __FILE__, __LINE__);
:o
It doesn't work with TP/SMF, though. I get a "Hacking attempt..." error.
Is there anything I can do to get around it?
Yeah, I tend to try keeping them simple - just so i understand what they do lol! :)
WHere did you insert the code? in a php article or block?
It's in an article. It's a huge article(35K+), that is really becoming a full-fledged program on its own. :)
I think I'm going to go with two queries, even though it's not as elegant. Less confusing and, well, I can get it to work. :)
Thanks for the help. There have been a number of times when I've come up against things I couldn't figure out and started to post here. By the time I had everything spelled out, I realized what the solution was on my own and didn't need to actually post the question. :)
Well, your posts are always interesting :) So even if it wasent nessesary for you to ask them and write the post, it's a pleasure to read them :)
Cant say how many of your things i have tested, and learn on the way to :)
Thank you, ma'am. I'm glad that I've been able to help folks along the way. I've learned a lot from reading the posts of others, too. I guess that's what this whole thing is about. :)
I've added the code with the two queries. It works and it's easy to understand, so I guess that's all that matters.
Sometimes you need to start from scratch...if a query is too complex maybe restructuring the tables is better. So that getting what you want is planned a bit before actually writing the query. I often land on that.