Show data from a one-to-many relationship

Question

I have 2 tables with a one-to-many relationship.

Table 1:

_________________
| id | question  | 
|____|___________|
|  1 | question1 |
|  2 | question2 |
|____|___________|

Table 2:

______________________________
| id | answer  | question_id |
|____|_________|_____________|
|  1 | answer1 |      1      |
|  2 | answer2 |      1      |
|____|_________|_____________|

While question_id is the foreign key.

I'm trying to show the data like that:

      question1
answer1     answer2

So question then the answers for this particular question.

Here is the code I have:

$query = "SELECT questions.*, answers.* FROM questions INNER JOIN answers ON questions.id = answers.question_id";

$run = mysqli_query($connect, $query);

while($data = mysqli_fetch_assoc($run)){
    echo $data['question'] . '<br>';
    echo $data['answer'] . '<br>';
}

This returns:

question1
answer1
question1
answer2

So how to show the question only once with the related answers to this particular question?

There are many ways to do it. One of them is simply nest two queries — Ken Lee, Jul 18 '21 at 05:08

score 2 · Accepted Answer · edited Jul 18 '21 at 08:30

2

MySQL's GROUP_CONCAT function might come in handy here:

$query = <<<QUERY
SELECT q.question, GROUP_CONCAT(a.answer ORDER BY q.id) answers
FROM questions q
         INNER JOIN answers a ON q.id = a.question_id
GROUP BY q.question
QUERY;

$run = mysqli_query($connect, $query);

while ($data = mysqli_fetch_assoc($run)) {
    echo htmlspecialchars($data['question'], ENT_QUOTES | ENT_HTML5), "<br>\n";
    $answers = explode(',', $data['answers']);

    echo '<pre>';
    for ($i=0, $iMax = count($answers); $i < $iMax; $i++) {
        if ($i > 0) echo "<br>\n";
        echo '&nbsp;&nbsp;&nbsp;&nbsp;', htmlspecialchars($answers[$i], ENT_QUOTES | ENT_HTML5);
    }
    echo '</pre>';
}

edited Jul 18 '21 at 08:30

hakre

193,403
52
435
836

answered Jul 18 '21 at 05:19

Tim Biegeleisen

502,043
27
286
360

It's returning 1 question and all the answers. I want to return each question once with its related answes – user_m Jul 18 '21 at 05:57
I copied/pasted the whole code and it returned the 1st question and all answers for all questions. Instead of each question with its answers – user_m Jul 18 '21 at 06:12
@user_m You're right, I messed up. I added a `GROUP BY` clause. It should work now. – Tim Biegeleisen Jul 18 '21 at 06:14

Show data from a one-to-many relationship

1 Answers1