How to list questions and asociated answers

[m2244]

Hello,

I am trying to get a list of questions stored in a table. There is a second table that has the related answers. When I run the following query I only get the questions, each one listed a numer of times.

How can I list each question once followed by all of the answers for that question?

select name from mp_question A
inner join mp_question_answers B
on A.category = B.question
where A.category = 107

[KULP]

Here is what came to mind for me.

$allQuestions = mysql_query("SELECT * FROM questions", $connection );

while($oneQuestion = mysql_fetch_array($allQuestions))
{
      echo $oneQuestion["question_text"];
      $allAnswers = //Select everything from answer table with questionID

      while($oneAnswer = mysql_fetch_array($allAnswers)
      {
              echo $oneAnswer["answer_text"];
       }
}

[Old Pedant]

Well, for starters, you need to SELECT the appropriate columns from the two tables.

SELECT A.name, B.answerText 
FROM mp_question A INNER JOIN mp_question_answers B
on A.category = B.question
where A.category = 107

But this seems weird to me: Why would you match the value of B.question with the value of A.category???

Maybe you need to show us your schema? All the fields in both tables?

[Old Pedant]

Oh...and except for a very very few odd circumstances, *NEVER* nest one query inside of another, as you did in your second post. HORRIBLY expensive!

[KULP]

Originally Posted by Old Pedant

Oh...and except for a very very few odd circumstances, *NEVER* nest one query inside of another, as you did in your second post. HORRIBLY expensive!

Are you referring to my post (I'm the 2nd post) or his? If mine, how would you go around it?

[Fou-Lu]

You use joins and let the software structure accordingly.
If you do a query within a loop, it would execute the query for the number of loops. If you had 60 questions, that means you would loop the inner query 60 times which is very expensive.
Pedant's first post there shows the join in question (with the ambiguity of the columns on the join mentioned).

So if I had a questions, and options table (so I could create a radio group for example), that would be similar to so using PHP to format it:

PHP Code:

$sQry = 'SELECT q.questionID, q.questionDescription, qo.optionID, qo.option
            FROM question q
            INNER JOIN questionoption qo ON qo.questionID = q.questionID
            ORDER BY q.questionID ASC';
if ($qry = mysql_query($sQry))
{
    $iLast = -1;
    if (mysql_num_rows($qry) > 0)
    {
        while ($row = mysql_fetch_assoc($qry))
        {
            if ($row['questionID'] != $iLast)
            {
                // question has changed
                if ($iLast > -1)
                {
                    print '</select>';
                }
                $iLast = $row['questionID'];
                printf('<label for="question_%d">%s</label>', $iLast, $row['questionDescription']);
                printf('<select name="question_%d" id="question_%d">', $iLast, $iLast);
            }
            printf('<option value="%s">%s</option>', $row['optionID'], $row['option']);
        }
        print('</select>');
    }
} 


I think I biffed the HTML in there, but that should give you the idea.

[Old Pedant]

Well, aside from the fact that you said you were going to use radio buttons and then changed to use a <select>, it looks right to me. Even with my limited knowledge of PHP.

[Fou-Lu]

Originally Posted by Old Pedant

Well, aside from the fact that you said you were going to use radio buttons and then changed to use a <select>, it looks right to me. Even with my limited knowledge of PHP.

Lols I wonder if that's what I was thinking of when I said I think I biffed the HTML. I knew something didn't look quite right. Something still doesn't look quite right though :P; I have a feeling there will be possible outcomes that could leave tags orphaned somewhere.

[Old Pedant]

I don't think so. I use essentially the same coding in ASP and it works.

Well, you probably want to slip a <br/> or <br/><br/> in there somewhere.

Either just before the <label> or just after the </select>.

But other than that...

[KULP]

Can you explain how JOINs work?

I think I understand the syntax of this, but how does this store in $sQry? Is this a 2D array? In other words, how do I access each value? Sorry, this is probably a stupid question. Also, did you memorize how to write this off the top of your head?

PHP Code:

$sQry = 'SELECT q.questionID, q.questionDescription, qo.optionID, qo.option
            FROM question q
            INNER JOIN questionoption qo ON qo.questionID = q.questionID
            ORDER BY q.questionID ASC'; 


EDIT: I tried this in my own project and I get the error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Simpolls\user.php on line 37

I have tables: users, polls, and choices. Polls contains userid, and choices contains pollid. I'm trying to get all polls with userid $currentid and then join those results with the choices for each poll (pollid). 'date' is a timestamp value in my polls table, if that sounds right. Hmm...

PHP Code:

$result = mysql_query('SELECT *
                FROM polls p WHERE p.userid = "' . $currentid . '" 
                INNER JOIN choices c ON c.pollid = p.pollid
                ORDER BY p.date DESC', $connection);
                
                while($row = mysql_fetch_array($result))
                {
                    echo $row['question'];
                } 


[KULP]

I finally got the query to work after an hour or two of messing with it / research! Now I have it echoing the question and first choice, but how do I access choice 2 (I have 2 choices in the choice table, both with pollid as 1.) This works, but does it look right? I just feel weird fetching an array inside of an array.

PHP Code:


                $qry = 'SELECT * FROM polls
                INNER JOIN choices ON polls.id = choices.pollid
                WHERE polls.userid = 1000';
                
                $result = mysql_query($qry, $connection);
                
                if($result === FALSE)
                {
                    die(mysql_error()); 
                }

                while($row = mysql_fetch_array($result))
                {
                    echo $row['question'];
                    echo $row['choice'];
                    
                    while($row = mysql_fetch_array($result))
                    {
                        echo $row['choice'];
                    }
                } 


/////////////////////////////////////////////////////////////////////

EDIT x12312 :

Nevermind I figured it out, so far!