How to encode JSON after fetching data from 2 tables using Mysql Php?

Question

// Fetching Name Of Exam
$sql = "SELECT distinct(`nameofexam`) FROM `marks` WHERE `OrgId`=$schoolid AND `Admno`=$Admno";
$result = $objcon->query($sql);

if ($result->num_rows > 0) {
  $row1 = array();
  // output data of each row
  $array1 = array();
  $json = array();
  while ($row = $result->fetch_assoc()) {
    $exam = $row["nameofexam"];
    $json[] = $exam;

    header('Content-type: application/json');
    // Fetching Exam details

    $sql1 = "SELECT  distinct Admno,Class,subject,`markobtained`,Position
            FROM    ( SELECT  @r:= CASE WHEN @e = nameofexam THEN @r + CASE WHEN @p = `markobtained` THEN 0 ELSE @i END ELSE 1 END Position,
                        @i:= CASE WHEN @p = `markobtained` THEN @i + 1 ELSE 1 END incr,
                        @e:= nameofexam,
                        @p:= `markobtained`, 
                        Admno,
                        Class,
                        nameofexam,
                        subject,
                        `markobtained`
                      FROM    marks,
                        (SELECT @e:= '') e,
                        (SELECT @r:= 0) r,
                        (SELECT @p:= 0) p,
                        (SELECT @i:= 0) i
                      ORDER BY nameofexam, `markobtained` desc
                    ) T 
            WHERE `Admno`=$Admno AND `nameofexam`='$exam'
            ORDER BY `subject`,position";
    $result1 = $objcon->query($sql1);
    if ($result1->num_rows > 0) {

      while ($row1 = $result1->fetch_assoc()) {

        array_push($json, $row1);
      }
    }
  }

  header('Content-type: application/json');
  echo json_encode($array1, $response_array);
} else {
  echo "No Exam Details Avalable";
}

I expect an output like this. The program logic is simple. First pick the exam name from the table1 and then fetch the corresponding mark sheet of that exam from another table and encode it. I need to encode multiple rows from mysql into json using php .Please help me...

Expected Output :

{"Onam Exam":[{"Admno": "3123","Class": "LKG-A","subject": "english ","markobtained": "50","Position": "1" }] }

Answer 1

At last got output. Here is the working code

 <?php
    $sql = "SELECT distinct(`nameofexam`) FROM `marks` WHERE `OrgId`=$schoolid AND `Admno`=$Admno";
    $result = $objcon->query($sql);

    if ($result->num_rows > 0) {
      $row1 = array();
      // output data of each row
      $array1 = array();
      $json = array();
       $j=0; 
      while ($row = $result->fetch_assoc()) {
        $exam = $row["nameofexam"];

        header('Content-type: application/json');
        // Fetching Exam details
        $sql1 = "SELECT  distinct Admno,Class,subject,`markobtained`,Position
                FROM    ( SELECT  @r:= CASE WHEN @e = nameofexam THEN @r + CASE WHEN @p = `markobtained` THEN 0 ELSE @i END ELSE 1 END Position,
                            @i:= CASE WHEN @p = `markobtained` THEN @i + 1 ELSE 1 END incr,
                            @e:= nameofexam,
                            @p:= `markobtained`, 
                            Admno,
                            Class,
                            nameofexam,
                            subject,
                            `markobtained`
                          FROM    marks,
                            (SELECT @e:= '') e,
                            (SELECT @r:= 0) r,
                            (SELECT @p:= 0) p,
                            (SELECT @i:= 0) i
                          ORDER BY nameofexam, `markobtained` desc
                        ) T 
                WHERE `Admno`=$Admno AND `nameofexam`='$exam'
                ORDER BY `subject`,position";
        $result1 = $objcon->query($sql1);
        $test_array=array();

        if ($result1->num_rows > 0) {
        $i=0;       
          while ($row1 = $result1->fetch_assoc()) {
              $test_array[$i]["Admno"]=$row1["Admno"];
              $test_array[$i]["Class"]=$row1["Class"];
              $test_array[$i]["subject"]=$row1["subject"];
              $test_array[$i]["markobtained"]=$row1["markobtained"];
              $test_array[$i]["Position"]=$row1["Position"];

             $i++;


          }

     $response[$j]=array(
            $exam=> $test_array
    );  
    $j++;

        }

            }

      header('Content-type: application/json');
      echo json_encode($response);
    } else {
      echo "No Exam Details Avalable";
    }
      }
    ?> 

Output

[{"Onam Exam":[{"Admno":"3123","Class":"LKG-A","subject":"english ","markobtained":"50","Position":"1"},{"Admno":"3123","Class":"LKG-A","subject":"Physics","markobtained":"10","Position":"18"}]},{"Xmas Exam":[{"Admno":"3123","Class":"LKG-A","subject":"Chemistry","markobtained":"9","Position":"2"},{"Admno":"3123","Class":"LKG-A","subject":"Hindi","markobtained":"100","Position":"21"},{"Admno":"3123","Class":"LKG-A","subject":"Physics","markobtained":"99","Position":"1"}]}]