I have created a quiz application using codeigniter 3 that will get data from 3 tables in MySQL (quiz_table,question_table and answer_table) to display the 4 quiz questions and their answers.Below is my SQL Query,

 function getSingleQuizQuestionDataFromDB($quizId)
{        //insert query
    try {
        $this->db->select('answer_table.quizId');
        $this->db->select('answer_table.questionId');
        $this->db->select('question_table.questionTitle');
        $this->db->select('question_table.correctAnswer');
        $this->db->select('answer_table.answerId');
        $this->db->select('answer_table.answer');
        $this->db->from('answer_table');
        $this->db->where('answer_table.quizId',$quizId);
        $this->db->join('question_table','answer_table.questionId= question_table.questionId','LEFT');
     
        //$this->db->group_by(['answer_table.quizId', 'answer_table.questionId']);
        $result = $this->db->get();


        $singleQuizQuestionData= $result->result_array();
        return $singleQuizQuestionData;
    } catch (Exception $e) {
        // log_message('error: ',$e->getMessage());
        return;
    }
}

Below is the response I get,

{
 "singleQuizQuestionData": [
  {
     "quizId": "68",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answerId": "269",
     "answer": "q1q1a1"
  },
  {
     "quizId": "68",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answerId": "270",
     "answer": "q1q1a2"
  },
  {
     "quizId": "68",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answerId": "271",
     "answer": "q1q1a3"
  },
  {
     "quizId": "68",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answerId": "272",
     "answer": "q1q1a4"
  },
  {
     "quizId": "68",
     "questionId": "77",
     "questionTitle": "q1q2",
     "correctAnswer": "q1a",
     "answerId": "273",
     "answer": "q1q2a1"
  },
  {
     "quizId": "68",
     "questionId": "77",
     "questionTitle": "q1q2",
     "correctAnswer": "q1a",
     "answerId": "274",
     "answer": "q1q2a2"
  },
  {
     "quizId": "68",
     "questionId": "77",
     "questionTitle": "q1q2",
     "correctAnswer": "q1a",
     "answerId": "275",
     "answer": "q1q2a3"
  },
  {
     "quizId": "68",
     "questionId": "77",
     "questionTitle": "q1q2",
     "correctAnswer": "q1a",
     "answerId": "276",
     "answer": "q1q2a4"
  },
  {
     "quizId": "68",
     "questionId": "78",
     "questionTitle": "q1q3",
     "correctAnswer": "q1a",
     "answerId": "277",
     "answer": "q1q3a1"
  },
  {
     "quizId": "68",
     "questionId": "78",
     "questionTitle": "q1q3",
     "correctAnswer": "q1a",
     "answerId": "278",
     "answer": "q1q3a2"
  },
  {
     "quizId": "68",
     "questionId": "78",
     "questionTitle": "q1q3",
     "correctAnswer": "q1a",
     "answerId": "279",
     "answer": "q1q3a3"
  },
  {
     "quizId": "68",
     "questionId": "78",
     "questionTitle": "q1q3",
     "correctAnswer": "q1a",
     "answerId": "280",
     "answer": "q1q3a4"
  },
  {
     "quizId": "68",
     "questionId": "79",
     "questionTitle": "q1q4",
     "correctAnswer": "q1a",
     "answerId": "281",
     "answer": "q1q4a1"
  },
  {
     "quizId": "68",
     "questionId": "79",
     "questionTitle": "q1q4",
     "correctAnswer": "q1a",
     "answerId": "282",
     "answer": "q1q4a2"
  },
  {
     "quizId": "68",
     "questionId": "79",
     "questionTitle": "q1q4",
     "correctAnswer": "q1a",
     "answerId": "283",
     "answer": "q1q4a3"
  },
  {
     "quizId": "68",
     "questionId": "79",
     "questionTitle": "q1q4",
     "correctAnswer": "q1a",
     "answerId": "284",
     "answer": "q1q4a4"
  }
 ]
}

The response I get is fine but is it possible to get the common ones out and make it like this, (this is shown only for question 1 but should have for all 4 questions)

{
 "singleQuizQuestionData": [
   "quizId": "68",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
  "answers":[
  {
    "answerId": "269","answer": "q1q1a1",
  },
  {
    "answerId": "270","answer": "q1q1a3",
  },
  {
    "answerId": "271","answer": "q1q1a3",
  },
  {
    "answerId": "272","answer": "q1q1a4",
  },
],
]}

CodePudding user response：

Easiest is to first get only the questions from the questions table, then loop over the result set, and for each question get the answers from the answers table and add these to an answers array underneath the corresponding question:

$this->db->select('quizId');
$this->db->select('questionId');
$this->db->select('questionTitle');
$this->db->select('correctAnswer');
$this->db->from('question_table');
$this->db->where('quizId',$quizId);
$result = $this->db->get()->result_array();

foreach ($result as $key => $row) {
    $this->db->select('answerId');
    $this->db->select('answer');
    $this->db->from('answer_table');
    $this->db->where('questionId',$row['questionId']);
    $result[$key]['answers'] = $this->db->get()->result_array();
}

return $result;