Stuck with a loop problem

[john2121]

Hello folks!

 

I have a mysql table with questions and another one with multiple choice answers. The common key is QuestionID. Some questions have 2 answers, some 3, and some 4. I can display a list of all questions on my page but I am unable to display the related answers below them.  I have been trying since yesterday but I just can't get it right.  It seems to be a loop related problem. Could you give me any pointers?

 

Thank you!

John

[Maq]

Can you show us the troubled code, what's happening, and what you want to happen?

 

(Be careful where you post, this thread was in the wrong section but I moved it)

[gizmola]

--Holding card to my turban

 

"The problem is .... on line... 32!!!"

 

Yes check line 32.

 

 

[Maq]

--Holding card to my turban

 

"The problem is .... on line... 32!!!"

 

Yes check line 32.

 

 

 

Thx because I left mine at home this morning ;/

[john2121]

I apologize for posting in the wrong section.  Here is what I want:

 

Question 1

- Answer Choice 1

- Answer Choice 2

Answer Choice 3

 

Question 2

- Answer Choice 1

- Answer Choice 2

 

The answers to the first question have a QuestionID value of 1.  The answers to the second question have a QuestionID value of 2.

 

I didn't know how to do it with one recordset so I created two - one for questions and one for answers.

 

// for questions
mysql_select_db($database_connProj, $connProj);
$query_Recordset1 = "SELECT Questions.Question_ID, Questions.Question FROM Questions";
$Recordset1 = mysql_query($query_Recordset1, $connProj) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

// for answers
mysql_select_db($database_connProj, $connProj);
$query_Recordset2 = "SELECT Answers.Answer_ID, Answers.Question_ID, Answers.Answer, Answers.Value FROM Answers";
$Recordset2 = mysql_query($query_Recordset2, $connProj) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

I am not sure how to do a foreach loop here so I tried a do while loop and a nested loop.

<?php do { ?> 
          <p><?php echo $row_Recordset1['Question_ID']; ?>   
              php echo $row_Recordset1['Question']; ?></p>

    		<?php do { ?>
                         <form id="form1" method="post" action="">
                        <input type="radio" name="<?php echo "radio" . $row_Recordset2['Question_ID'];?>" id="radiobuttons" value="<?php echo $row_Recordset2['Value']; ?>" />
                        <label for="radiobuttons"><?php echo $row_Recordset2['Answer']; ?></label>
                </form>
                <?php } while ($row_Recordset2['Question_ID'] == $row_Recordset1['Question_ID']); ?>

    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

 

I am not sure how to change it.

 

Thanks!

[gizmola]

First on the mysql_select_db() you only need to do that once, and only need to call it again if you're going to change the selected database.  Both these tables are in the same db, so just make the connection, select your db, and you're good for any number of queries.

 

The most efficient answer is to join the tables together and get one result set.  That is complicated and would require a lot of changes to your code, so I won't suggest you do that.

 

The simpler answer is:  Query for questions.  Then start fetching and inside your loop do a nested query for the related answers.

 

while ($row = mysql_fetch_assoc($result) {
  //output your question markup
  $query = "SELECT Answer_ID, Value FROM Answers WHERE Question_ID = $row['Question_ID']";
  $result2 = mysql_query($query);
  if ($result) {
    while ($row2 = mysql_fetch_assoc($result2) {
      //output your answer markup
    }
  }

 

[john2121]

Thank you for your response.  I'll try it out.

 

By joining the tables do you mean something like this:

SELECT Questions.Question_ID, Questions.Question, Answers.AnswerID, Answers.Answer, Answers.Value FROM Questions INNER JOIN Answers ON Questions.Question_ID = Answers.Question_ID

 

There too my problem was how to make the answers appear as a group below each question.  Is a foreach loop used in such situations?

 

Thanks!

 

[Jessica]

Thank you for your response.  I'll try it out.

 

By joining the tables do you mean something like this:

SELECT Questions.Question_ID, Questions.Question, Answers.AnswerID, Answers.Answer, Answers.Value FROM Questions INNER JOIN Answers ON Questions.Question_ID = Answers.Question_ID

 

There too my problem was how to make the answers appear as a group below each question.  Is a foreach loop used in such situations?

 

Thanks!

 

 

That's how I would do it, and order by quest_id and answer_id. Then a foreach or while depending on how you've written it. You'd print the question once, and keep looping through printing answers, until you get to a new question id.

[gizmola]

I commend you for trying to attack the most efficient solution.

 

Yes, when you join 2 tables together you get a row everytime the two columns can join on the join condition, so you would get a row for every answer of every question.  So long as you ORDER BY this select by Question_ID, AnswerID you'll have a result set you can work with, and you can then save the initial question id, output your question markup, then output your answer markup for each answer, until you see that the question_id changed. 

 

You don't have to fetch all the rows and foreach through them if you don't want to, you could accomplish this in a single while ($row = mysql_fetch...) loop.

 

Structurally, this is where functions shine, because you write a function that takes a row param and outputs your question markup, and another that takes a row and outputs your answer markup, and you will be left with a tiny easy to understand loop that makes a couple of function calls.  It's also a step towards the best practice of decoupling your logic from your presentation code, in that you would move all that presentation code into a function, which could easily include a simple template file that had just the basic markup and variable replacement you need.

[john2121]

Thanks, jesirose for your response.  And, especially, thank you gizmola, the moderator,for taking the time to provide such help.  Great site this!