具有多个列的API php转到一个id

Question

In my database I have multiple columns like this

Questions

question one with questionID 1
question two with questionID 2
...

Answers

answer one with questionID 1
answer two with questionID 1
answer one with questionID 2
answer two with questionID 2

To retrieve them I usually works as the example below:

$sql = "SELECT * FROM Questions LEFT JOIN Answers ON Questions.questionID = Answers.questionID";

getting this outcome:

[{
    "questionID": "1",
    "question": "Test Question",
    "potID": "1",
    "optionID": "1",
    "optionValue": "answer 1",
    "isCorrect": "0"
}, {
    "questionID": "1",
    "question": "Test Question",
    "potID": "1",
    "optionID": "2",
    "optionValue": "answer 2",
    "isCorrect": "1"
}]

How can I get the data as following

[{
    "questionID": "1",
    "question": "Test Question",
    "potID": "1"
{Answers: [
    "optionID": "1",
    "optionValue": "answer 1",
    "isCorrect": "0"
}, {
    "optionID": "2",
    "optionValue": "answer 2",
    "isCorrect": "1"
]}
}]

Answer 1

I suggest you to use cycle inside another cycle to collect all answer to specific question. How I usually do this using PHP:

$questions = [
    ['questionID' => 1, 'question' => 'What is my name?', 'answers' => []]
];

$answers = [
    ['answerID' => 1, 'value' => 'John', 'questionID' => 1, 'isCorrect' => 1],
    ['answerID' => 2, 'value' => 'Paul', 'questionID' => 1, 'isCorrect' => 0]
];

foreach ($questions as $question) {
    foreach ($answers as $answer) {
        if ($answer['questionID'] == $question['questionID']) {
            $question['answers'][] = $answer;
        }
    }
}

// output should be something like this
[
    'questionID' => 1, 'question' => 'What is my name?', 'answers' => [
        ['answerID' => 1, 'value' => 'John', 'questionID' => 1, 'isCorrect' => 1],
        ['answerID' => 2, 'value' => 'Paul', 'questionID' => 1, 'isCorrect' => 0]
    ]
]

You need to make something similar to this using your language.