Returning Complex JSON structure in PHP

Question

I am writing a PHP script that can return me a JSON file in the below format. I want to create this structure by fetching data from my database tables. I am using SlickQuiz plugin and having a hard time to create an array of objects (i.e. options) inside the question object... that is again included as an array.

{
    "info": {
        "name":    "This is Exam name",
        "main":    "Find out with this super crazy knowledge",
        "results": "Get ready",
        "level1":  "Result Poor",
        "level2":  "Result Average",
        "level3":  "Result Good",
        "level4":  "Result Very Good",
        "level5":  "Result Great" 
    },
    "questions": [
            "q": "Which is the letter A in the English alphabet?",
            "a": [
                {"option": "8",      "correct": false},
                {"option": "14",     "correct": false},
                {"option": "1",      "correct": true},
                {"option": "23",     "correct": false} 
            ],
            "correct": "This is correct",
            "incorrect": "It's the first letter of the alphabet."
        },
        { 
            "q": "Eureka Which of the following best represents your preferred breakfast?",
            "a": [
                {"option": "Bacon and eggs",               "correct": false},
                {"option": "Fruit, oatmeal, and yogurt",   "correct": true},
                {"option": "Leftover pizza",               "correct": false},
                {"option": "Eggs, fruit, toast, and milk", "correct": true} 
            ],
            "select_any": true,
            "correct": "<p><span>Nice!</span> Your cholestoral level is probably doing alright.</p>",
            "incorrect": "<p><span>Hmmm.</span> You might want to reconsider your options.</p>"
        },
        { 
            "q": "Eureka Where are you right now? Select ALL that apply.",
            "a": [
                {"option": "Planet Earth",           "correct": true},
                {"option": "Pluto",                  "correct": false},
                {"option": "At a computing device",  "correct": true},
                {"option": "The Milky Way",          "correct": true} 
            ],
            "correct": "<p><span>Brilliant!</span> You're seriously a genius, (wo)man.</p>",
            "incorrect": "<p><span>Not Quite.</span> You're actually on Planet Earth, in The Milky Way, At a computer. But nice try.</p>" 
        },
        { 
            "q": "How many Eureka of rain does Michigan get on average per year?",
            "a": [
                {"option": "149",    "correct": false},
                {"option": "32",     "correct": true},
                {"option": "3",      "correct": false},
                {"option": "1291",   "correct": false} 
            ],
            "correct": "<p><span>Eureka bananas!</span> I didn't actually expect you to know that! Correct!</p>",
            "incorrect": "<p><span>Fail.</span> Sorry. You lose. It actually rains approximately 32 inches a year in Michigan.</p>"
        },
        { 
            "q": "Is Earth bigger than a basketball?",
            "a": [
                {"option": "Yes",    "correct": true},
                {"option": "No",     "correct": false} 
            ],
            "correct": "<p><span>Eureka Job!</span> You must be very observant!</p>",
            "incorrect": "<p><span>ERRRR!</span> What planet Earth are <em>you</em> living on?!?</p>"
    ]
}

Here is how I tried to generate this using PHP.

function generateJSON($pdo){
     $response = array();
            $response["error"] = false;         
            $response["questions"] = array();

  $stmt = $pdo->prepare("SELECT * FROM questions");
  $stmt->execute();
  $result= $stmt->fetchAll();
  if($stmt->rowCount() > 0){
  foreach($result as $row) {
       $tmp = array();
       $tmp["id"] = $row["id"];
       $tmp["q"] = $row["question"];
       $tmp["correct"] = $row["question"];
       $tmp["incorrect"] = $row["subject_id"];
       $tmp["status"] = $row["level_id"];

       //Fetching the options
       $stmt2 = $pdo->prepare("SELECT * FROM question_options WHERE question_id = ".$tmp["id"]);
       $stmt2->execute();
       $opt_result= $stmt2->fetchAll();
       foreach($opt_result as $opt_row) {
       $option = array();
       $option["option"] = $opt_row["option_text"];
       $option["correct"] = $opt_row["is_correct"] ==1;
       array_push($response["questions"], $option);
       }
       //End of fetching options for this question

       array_push($response["questions"], $tmp);
  }
  }
            echoRespnse(200, $response);
}

Answer 1

As mentioned in the comment by @apokryfos, you really should be looking at making your code more modular. The most common, and arguably the simplest, way to do this is by using classes.
A quick example of how you could structure this:

class Quiz {
    private $questions;
    public function addQuestion (Question $question)
    public function addGrade ($title, $score)
    public function serialize ()
}

class Question {
    private $text;
    private $alternatives = array ();
    private $correctString;
    private $incorrectString;
    public function setText ($text)
    public function addAlternative ($text, $correct)
    public function serialize ()
}

Fill this out as necessary, and add the few missing methods that you require. Making sure you return the necessary data in the serialize () functions, so that you can use them to build up the requested array.

Once that is done, then you can do something similar to this, using INNER JOIN to get all of the records in one query:

// Generate the quiz and set all of its properties.
$quiz = new Quiz ();

while ($row = $res->fetch_row ()) {
    if ($row['question_id'] != $oldID) {
        // Save the old (completed) question to the quiz.
        $quiz->addQuestion ($question);

        // Used to detect when we get to a new question.
        $oldID = $row['question_id'];

        $question = new Question ();

        // Add the question details here.
        $question->addTitle ();
        ......
    }

    // Each question has at least one alternative.
    $question->addAlternative ($row['alt'], $row['correct']};
}

// We need to ensure that we've added the last question to the quiz as well.
$quiz->addQuestion ($question);

$data = json_serialize ($quiz->serialize ());