How can I limit this mysql query to display column data only once?

Question

I need to display all the answer choices per question to the user so that he can select his answer.

Display/Markup

 <?php 
 $i=1; 
 $surveyQ="SELECT * FROM ve_survey_answers a 
 JOIN ve_survey_questions q 
 ON a.QuestionId=q.id";
 $surveyResult=mysqli_query($db, $surveyQ);
 while($survey=mysqli_fetch_array($surveyResult)){$i++;?>

<div class="div">
<input type="radio" name="useranswer" id="radio<?=$i;?>" class="radio" value='<?=$survey['AnswerId'];?>'/>
<label class="surveylabel" for="radio<?=$i;?>"><?=$survey['Answer'];?></label>
</div>
<?php } ;?>

The query above works but it shows like this:

What kind of pet do you prefer?
Cat
What kind of pet do you prefer?
Dog
What kind of pet do you prefer?
Bird
What kind of pet do you prefer?
Reptile

How can I make it display like this?

What kind of pet do you prefer?
Cat
Dog
Bird
Reptile

I think you need to look more closely at the HTML output, as it's not clear from that code what's going to happen. If you can do anything to de-clutter that, it'd help. — tadman
– tadman, Commented Apr 26, 2017 at 1:22

Tim Biegeleisen · Accepted Answer · 2017-04-26 02:23:34Z

1

I made two changes to the logic of your code. First, I added an ORDER BY clause to the query, such that questions are grouped together. This means that when you iterate the result set, answers for a given question should come in a continuous sequence. Next, I added a variable to keep track of what the current question is. The question is only printed once, at the start of a block of answers for a new question.

<?php 
    $i=1; 
    $surveyQ = "SELECT * FROM ve_survey_answers a 
                INNER JOIN ve_survey_questions q 
                    ON a.QuestionId = q.id
                ORDER BY a.QuestionId";
    $surveyResult = mysqli_query($db, $surveyQ);
    $question = "";
    while ($survey=mysqli_fetch_array($surveyResult)) {
        $i++;
        $curr_question = $survey['Question'];
        if ($question != $curr_question) {
            $question = $curr_question;
            echo "<h5 style='font-weight: bold;'>$question</h5>";
        };
        echo "<div class=\"div\">
              <input type=\"radio\" name=\"useranswer\" id=\"radio<?=$i;?>\" class=\"radio\" value=\"<?=$survey['AnswerId'];?>\"/>
              <label class=\"surveylabel\" for=\"radio<?=$i;?>\"><?=$survey['Answer'];?></label>
             </div>";
    }
?>

edited Apr 26, 2017 at 2:23

answered Apr 26, 2017 at 1:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sebastian Farham Over a year ago

This will not run properly part of your code is outside the php tags

Sebastian Farham Over a year ago

I will edit your code if you dont mind. Yours dont run.

Tim Biegeleisen Over a year ago

@SebastianFarham I copied from your question. Yes, feel free to edit, but hopefully you get the idea of what I am trying to do here.

Sebastian Farham Over a year ago

Edit made. Thanks.

Sebastian Farham Over a year ago

how would this fit with your $curr_question = $survey['Question']; variable?

Collectives™ on Stack Overflow

How can I limit this mysql query to display column data only once?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related