title: Creating proper JSON using mySQL and php
body: <p>I am pretty sure I am mixing several tasks together, but please be patient, I am a newby in mySQL and php.</p>
  
  <p>I am using <code>json_encode</code> to create JSON from mySQL query.
  But before it is echoed, I would like to modify the JSON, to add more valuable data.
  Currently my JSON looks like this:</p>
  
  <pre><code>[
    {
      "question": "1",
      "questionText": "question text 1",
      "categoryID": "1",
      "answerID": "1",
      "answerText": "answer text 1",
      "isTrue": "1"
    },
    {
      "question": "1",
      "questionText": "question text 1",
      "categoryID": "1",
      "answerID": "2",
      "answerText": "answer text 2",
      "isTrue": "0"
    },
    {
      "question": "1",
      "questionText": "question text 1",
      "categoryID": "1",
      "answerID": "3",
      "answerText": "answer text 3",
      "isTrue": "0"
    },
    {
      "question": "1",
      "questionText": "question text 1",
      "categoryID": "1",
      "answerID": "4",
      "answerText": "answer text 4",
      "isTrue": "0"
    },
    {
      "question": "2",
      "questionText": "question text 2",
      "categoryID": "2",
      "answerID": "1",
      "answerText": "answer text 1",
      "isTrue": "0"
    },
    {
      "question": "2",
      "questionText": "question text 2",
      "categoryID": "2",
      "answerID": "2",
      "answerText": "answer text 2",
      "isTrue": "1"
    },
    {
      "question": "2",
      "questionText": "question text 2",
      "categoryID": "2",
      "answerID": "3",
      "answerText": "answer text 3",
      "isTrue": "0"
    },
    {
      "question": "2",
      "questionText": "question text 2",
      "categoryID": "2",
      "answerID": "4",
      "answerText": "answer text 4",
      "isTrue": "0"
    }
  ]
  </code></pre>
  
  <p>I would like it to look like this (pseudo code):</p>
  
  <pre><code>    exam:{
      questions:[
          question:{
              questionID: string
              questionTest: string
              categoryID: string
              correctAnswerID: string
              chosenAnswerID: string
              answers:[
                  answer:{
                      answerID = string
                      answerText = string
                      isTrue = bool
                  }
                  answer:{}
              ]
          }
          question:{}
       ]
       categoryID: string
   }
  </code></pre>
  
  <p>and here is the code so far:</p>
  
  <pre><code>&lt;html&gt;
  &lt;body&gt;
  
  &lt;?php
      header('Content-Type: text/html; charset=utf-8');
      $con=mysqli_connect("localhost","root","root","Theory");
      // Check connection
      if (mysqli_connect_errno())
      {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
      $result = mysqli_query($con,"SELECT `questions`.`questionID` AS questionID,
                             `questions`.`questionText` AS questionText,
                             `questions`.`categoryID` AS categoryID,
                             `answers`.`answerID` AS answerID,
                             `answers`.`answerText` AS answerText,
                             `answers`.`isTrue` AS isTrue
                                   FROM `questions`,`answers`
                                   WHERE `questions`.`questionID` = `answers`.`questionID`");
  
      if (!$result)
      {
          die('Error: ' . mysqli_error($con));
      }
  
     $rows = array();
     while($r = mysqli_fetch_assoc($result)) {
          $rows[] = $r;
     }
     print json_encode($rows);
  
      mysqli_close($con);
      ?&gt;
  
  &lt;/body&gt;
  &lt;/head&gt;
  </code></pre>