Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    1
    Thanked 1 Time in 1 Post

    How to list questions and asociated answers

    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?

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

  • #2
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Here is what came to mind for me.

    Code:
    $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"];
           }
    }

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    Well, for starters, you need to SELECT the appropriate columns from the two tables.
    Code:
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    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!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Quote Originally Posted by Old Pedant View Post
    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?

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    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...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    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:
    Code:
    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'];
                    } 
    Last edited by KULP; 12-02-2012 at 09:03 AM.

  • #11
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    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!
    Last edited by KULP; 12-03-2012 at 12:24 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •