Multiple MySQL table to json_encode

Question

I have 3 different tables in my database called consoleConsole, consoleModel, and consoleGame. Then what I want to do is that each console will have a loop inside for its models, and each models will have another loop inside for its games like this:

[
   {
      "Console":"PlayStation",
      "Information":[
         {
            "Model":"PlayStation 3",
            "Title":[
               {
                  "Game":"007 Legends",
                  "Publisher":"Electronic Arts"
               },
               {
                  "Game":"Ace Combat: Assault Horizon",
                  "Publisher":"Namco"
               }
            ]
         },
         {
            "Model":"PlayStation 2",
            "Title":[
               {
                  "Game":"007: Agent of Fire",
                  "Publisher":"Electronic Arts"
               },
               {
                  "Game":"Ace Combat 4: Shattered Skies",
                  "Publisher":"Namco"
               }
            ]
         },
         {
            "Model":"PlayStation 1",
            "Title":[
               {
                  "Game":"007 Racing",
                  "Publisher":"Electronic Arts"
               },
               {
                  "Game":"Ace Combat",
                  "Publisher":"Namco"
               }
            ]
         }
      ]
   },
   {
      "Console":"Wii",
      "Information":[
         {
            "Model":"Wii",
            "Title":[
               {
                  "Game":"007: Quantum of Solace",
                  "Publisher":"Activision"
               },
               {
                  "Game":"AC/DC Live: Rock Band Track Rack",
                  "Publisher":"MTV Games"
               }
            ]
         }
      ]
   },
   {
      "Console":"Xbox",
      "Information":[
         {
            "Model":"Xbox",
            "Title":[
               {
                  "Game":"AFL",
                  "Publisher":"Acclaim"
               },
               {
                  "Game":"American Chopper",
                  "Publisher":"Activision"
               }
            ]
         },
         {
            "Model":"Xbox 360",
            "Title":[
               {
                  "Game":"AFL Live",
                  "Publisher":"Electronic Arts"
               },
               {
                  "Game":"Akai Katana Shin",
                  "Publisher":"Cave"
               }
            ]
         }
      ]
   }
]

But sadly, I was not using my database with this one but instead I just wrote it straight in a php file.

EDIT

Anyways, moving on. I have modified my code and ended up like this.

<?PHP
    $consoleQuery = "SELECT * ".
    "FROM consoleConsole ".
        "JOIN consoleModel ".
                "ON consoleConsole.consoleId = consoleModel.consoleId ".
            "JOIN consoleGame ".
                "ON consoleModel.modelId = consoleGame.gameId";

$consoleResult = mysql_query($consoleQuery);

$consoleFields = array_fill_keys(array(
    'consoleName',
    ), null);

$modelFields = array_fill_keys(array(
    'modelName',
    ), null);

$console = array();
$rowConsole = array();

while ($rowConsole = mysql_fetch_assoc($consoleResult)) {
    $consoleId = $rowConsole['consoleId'];
    $modelId = $row['modelId'];
    if (isset($console[$consoleId]['Information'])) {
        $console[$consoleId]['Information'][] = array_intersect_key($rowConsole, $modelFields);
    }

    else {
        $console[$consoleId] = array_intersect_key($rowConsole, $consoleFields);
        $console[$consoleId]['Information'] = array(array_intersect_key($rowConsole, $modelFields));
    }
}

$console = array_values($console);
echo json_encode($console);

?>

I was able to produce an output but it doesn't look like the output above.

[
  {
    "consoleName": "PlayStation",
    "Information": [
      {
        "modelName": "PlayStation"
      },
      {
        "modelName": "PlayStation 2"
      },
      {
        "modelName": "PlayStation 3"
      },
      {
        "modelName": "PlayStation 3"
      }
    ]
  },
  {
    "consoleName": "Wii",
    "Information": [
      {
        "modelName": "Wii"
      },
      {
        "modelName": "Wii"
      }
    ]
  },
  {
    "consoleName": "Xbox",
    "Information": [
      {
        "modelName": "Xbox"
      },
      {
        "modelName": "Xbox 360"
      }
    ]
  }
]

Their relations: enter image description here

What my problem is right now, I can't add the Title of the each Game.

sql returns a "flat" table, if you want a more complex data structure, you'll need to build it yourself — Itai Sagi

ngreenwood6 ngreenwood6 · Accepted Answer · 2013-02-08T23:12:15

Ok so I have written up your solution. You have to be sure that the order by is included there because it assumes that you are ordering them with there items together. I also didnt know how your publisher was stored so I separated that out into a separate table (this will allow you to then get items by just there publisher as well), which is now 4 joins. Also on another note I have updated it to do inner joins as well. This way you will not get empty results for consoles that don't have any games assigned to them. If you want these you could simply change the joins so that it would give you those results as well. Let me know if this helps

//get all of the information
$query = '
    SELECT c.consoleId,c.consoleName,m.modelId,m.modelName,g.gameId,g.gameName,p.publisherId,p.publisherName
    FROM `consoleconsole` c
        INNER JOIN `consolemodel` m ON c.consoleId=m.consoleId
        INNER JOIN `consolegame` g ON m.modelId=g.modelId
        INNER JOIN `consolepublisher` p ON g.publisherId = p.publisherId
    ORDER BY c.consoleName, m.modelName, g.gameName
';

//get the results
$result = mysql_query($query);

//setup array to hold information
$consoles = array();

//setup holders for the different types so that we can filter out the data
$consoleId = 0;
$modelId = 0;

//setup to hold our current index
$consoleIndex = -1;
$modelIndex = -1;

//go through the rows
while($row = mysql_fetch_assoc($result)){
    if($consoleId != $row['consoleId']){
        $consoleIndex++;
        $modelIndex = -1;
        $consoleId = $row['consoleId'];

        //add the console
        $consoles[$consoleIndex]['console'] = $row['consoleName'];

        //setup the information array
        $consoles[$consoleIndex]['information'] = array();
    }

    if($modelId != $row['modelId']){
        $modelIndex++;
        $modelId = $row['modelId'];

        //add the model to the console
        $consoles[$consoleIndex]['information'][$modelIndex]['model'] = $row['modelName'];

        //setup the title array
        $consoles[$consoleIndex]['information'][$modelIndex]['title'] = array();
    }

    //add the game to the current console and model
    $consoles[$consoleIndex]['information'][$modelIndex]['title'][] = array(
        'game'      => $row['gameName'],
        'publisher' => $row['publisherName']
    );
}

echo json_encode($consoles);

Multiple MySQL table to json_encode

2 Answers