Geek asks and answers

Multiple MySQL table for json_encode

I have 3 different tables in my database consoleConsole, consoleModeland consoleGame. Then I want each console to have a loop inside its models, and each model will have a different loop inside for 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, unfortunately, I did not use my database with this, but instead, I just wrote it directly in the php file.

EDIT

In any case, move on. I changed 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 managed to create a way out, but it does not look higher.

[
  {
    "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 relationship: enter image description here

What is my problem now, I can not add the title of each game.

+5
source share
2 answers

, . , , , . , , ( ), 4 . , . , , . , , . ,

//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);
+3

php json, json . , , .

+3

More articles:


All Articles