Problem with a query

[JPDeni]

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

Code Select Expand

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.

[bloc]

Maybe something like..

Code Select Expand

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");

[JPDeni]

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.

Code Select Expand

$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__);


It doesn't work with TP/SMF, though. I get a "Hacking attempt..." error.

Is there anything I can do to get around it?

[bloc]

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?

[JPDeni]

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.

[G6Cad]

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

[JPDeni]

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.

[bloc]

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.