CODEWITHSUNDEEP
phpmysqljson
PatrickNY
PatrickNY

Reputation: 153

Issue with Nested JSON in PHP

I'm getting close to getting this JSON from a MySQL query right, but I'm having some difficulty. 

$results = [];

foreach ($getbill as $row) {
    $category = $district;
    $building = $row['building'];

    if (!isset($results[$category])) 
    {$results[$building] = ['category' => $building, 'values' => []];    }
        $results[$category] = ['category' => $building, 'values' => []];    
            $results[$row['building']]['students'][] = ['lastname' => $row['slast'], 'firstname' => $row['sfirst'], 'ens' => $row['selected'], 'part' => $row['instname'], 'division' => $row['sdiv'], 'festival' => $row['sfest']];
            }

echo json_encode(array_values($results));

The code above exports:

[{"category":"Belmont Elementary School","values":[],"students":[{"lastname":"jones","firstname":"melissa","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"smith","firstname":"melissa","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"b","firstname":"b","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"b","firstname":"b","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"chocolate","firstname":"Charlie","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"Shmow","firstname":"Joe","ens":"C","part":"Childrens Voice","division":"1","festival":"w"},{"lastname":"abrams","firstname":"Alysond","ens":"C","part":"Childrens Voice","division":"1","festival":"w"}]},{"category":"Parliament Place","values":[]},{"students":[{"lastname":"Jones","firstname":"Joe","ens":"B","part":"Trombone","division":"1","festival":"w"},{"lastname":"Smith","firstname":"Ally","ens":"B","part":"Alto Sax","division":"1","festival":"w"}]}]

It is grouping by School, however, the School has to be listed in the beginning, right before the student information. The finished product needs to be as follows, but I'm at a loss...

{"length":8,"schools":[{"name":"Test High School","students":[{"lastname":"Smith","firstname":"Allison","ens":"Band","part":"Bb Clarinet","division":"III","festival":"West"},{"lastname":"Jones","firstname":"Derek","ens":"Band","part":"Tuba/Sousaphone","division":"III","festival":"West"},{"lastname":"Johnson","firstname":"Matthew","ens":"Band","part":"Timpani","division":"III","festival":"West"},{"lastname":"Hughley","firstname":"Elizabeth","ens":"Band","part":"French Horn","division":"II","festival":"West"}]},{"name":"Test Elementary School","students":[{"lastname":"Jones","firstname":"Emmett","ens":"Band","part":"Bb Clarinet","division":"I","festival":"West"},{"lastname":"Aaren","firstname":"Owen","ens":"Band","part":"Tuba/Sousaphone","division":"I","festival":"West"},{"lastname":"Johns","firstname":"Sonia","ens":"Band","part":"French Horn","division":"I","festival":"West"},{"lastname":"Williams","firstname":"Nathaniel","ens":"Band","part":"Bb Clarinet","division":"I","festival":"West"}]}],"bill":120}

I assume that I can use a PHP variable to get the "Length" and "Bill" part correct by counting the records of the query and by multiplying by 15, but .. How do I squeeze in all of the correct JSON creation code in PHP?

Updated: Original Data Below

original data

UPDATE 2: I figured it out. It was the original comment from @Raymond that put me in the right direction. Thank you. I had to get rid of one line of my query, and I had to manually 'echo' the beginning and the end (length and cost). It's working! Thank you all for your help.

foreach ($getbill as $row) {
    if (!isset($results[$building])) {
        $results[$building] = ['name' => $row['building']];  
        }
        $results[$row['building']]['students'][] = ['lastname' => $row['slast'], 'firstname' => $row['sfirst'], 'ens' => $ens, 'part' => $row['instname'], 'division' => $age, 'festival' => $loc];
}

Upvotes: 2

Views: 249

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

Does this code example help you?

PHP objects will be converted into a JSON Object which is the {} part.
PHP array will be converted into a JSON Array which is offcource the [] part.

PHP code

<?php

$StdClass = new StdClass();
$StdClass->length = 10;
$StdClass->schools = array_values(array(1, 2));

var_dump(json_encode($StdClass));

?>

Result

string(29) "{"length":10,"schools":[1,2]}"

Edited because off comment:

Thank you for your quick reply, but sadly, I'm having a real tough time figuring that out. Also, there are all of these 'character counts' that I can't use.

Yes getting the [{..}] JSON format for schools and students it more tricky.
I advice you writing custom classes and use JsonSerializable to convert your custom Objects into JSON

PHP 7 code

<?php

class school implements JsonSerializable {
  private $name = null;
  private $students = array();

  private function __construct() {

  }

  public function __destruct() {
      $this->name = null;
      $this->students = null;
  }  

  // Use a static object because it will enable method chaining
  public static function getInstance() {
      return new school();
  }

  public function setName(string $name) : school { 
      $this->name = $name;
      return $this; // needed to chain
  }

  public function setStudent(Student $student) : school { 
      $this->students[] = $student;
      return $this; // needed to chain
  }  

  public function getName() : ?string {
      return $this->name;
  }

  public function getStudents() : ?array {
      return $this->students;
  }  

  public function __get($name) : mixed {
      return $this->name;
  }

  public function jsonSerialize() {
    return array(
        "name" => $this->name
      , "students" => $this->students
    );
  }  
}

class student implements JsonSerializable {
  private $lastname = null; 
  private $firstname = null;

  private function __construct() {

  }

  public function __destruct() {
      $this->lastname = null;
      $this->firstname = null;
  }

  // Use a static object because it will enable method chaining
  public static function getInstance() {
      return new student();
  }  

  public function setLastName(string $lastname) : student { 
      $this->lastname = $lastname;
      return $this; // needed to chain
  }  

  public function setFirstName(string $firstname) : student { 
      $this->firstname = $firstname;
      return $this; // needed to chain
  } 

  public function getFirstName() : ?string {
      return $this->firstname;
  }

  public function getLastName() : ?string {
      return $this->lastname;
  }  

  public function jsonSerialize() {
    return array(
        "lastname" => $this->lastname
      , "firstname" => $this->firstname
    );  
  }
}

$json = new stdClass();
$json->length = 10;


$json->schools = array(
    school::getInstance()
          ->setName('Test High School')
          ->setStudent(
              student::getInstance()
                     ->setFirstname('Smith') 
                     ->setLastname('Allison')
          )
          ->setStudent(
              student::getInstance()
                     ->setFirstname('Jones') 
                     ->setLastname('Derek')
          )          
    , 
    school::getInstance()
          ->setName('Test Elementary School')    
          ->setStudent(
              student::getInstance()
                     ->setFirstname('Jones') 
                     ->setLastname('Emmett')  
         ) 
);

var_dump(json_encode($json));

?>

p.s to get the code working on lower PHP versions remove the PHP's return type declarations

Results in this JSON format

{
    "length": 10,
    "schools": [{
        "name": "Test High School",
        "students": [{
            "lastname": "Allison",
            "firstname": "Smith"
        }, {
            "lastname": "Derek",
            "firstname": "Jones"
        }]
    }, {
        "name": "Test Elementary School",
        "students": [{
            "lastname": "Emmett",
            "firstname": "Jones"
        }]
    }]
}

Upvotes: 1

Manuel Pirez
Manuel Pirez

Reputation: 605

To make you're life easier i suggest you do something like 

$students_array = []; 
array_push($students_array, ["key"=>"data"]);

to get an array similar to what you want to get, check out the structure below. And so on with the nested ones, i think this will help, don't hesitate to ask !

json_encode(
                [
                    "length"=>/*sizeof($some_array)*/ 2,
                    "schools" =>
                    [
                        "name" => 'test highscool',
                        "students" => [
                            [
                                "lastname" => "Smith",
                                "firstname" => "Allison",
                                "ens" => "Band",
                                "part" => "Bb Clarinet",
                                "division" => "III",
                                "festival" => "West"],
                            [
                                "lastname" => "Jones",
                                "firstname" => "Derek",
                                "ens" => "Band",
                                "part" => "Tuba/Sousaphone",
                                "division" => "III",
                                "festival" => "West"
                            ]
                        ]
                    ],
                    [
                        "name" => 'test highscool_2',
                        "students" => [
                            [
                                "lastname" => "Smith 2",
                                "firstname" => "Allison 2",
                                "ens" => "Band 2",
                                "part" => "Bb Clarinet 2",
                                "division" => "III 2",
                                "festival" => "West 2"]
                        ]
                    ]
                ]
            );

Upvotes: 0

Related Questions