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
– Itai Sagi, Commented Feb 7, 2013 at 7:54

ngreenwood6 · Accepted Answer · 2013-02-08 23:12:15Z

3

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);

answered Feb 8, 2013 at 23:12

ngreenwood6

8,23611 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jahm Over a year ago

while($row = mysql_fetch_assoc($result)) gives Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given. By the way, in the INNER JOIN in $query, isn't it consoleModel and etc?

ngreenwood6 Over a year ago

well you should always do some error checking before trying to fetch the rows. But basically that means that your query is failing in this case. Did u make sure that the query lined up with your tables? I created that extra table in there "consolePublishers" and if you dont have it then it will fail. You will need to create that table or modify the query to give you the information you need. You can change the query line to "mysql_query($query) or die(mysql_error())" if you want to see what the actual error is.

ngreenwood6 Over a year ago

Yes it is consoleModel etc., but since mysql is case insensitive (in most cases) it doesnt really matter but you should update it to fit your table structure.

Jahm Over a year ago

Well, there's a bit of a catch. I've added PlayStation Portable to consoleModel having a modelId = 8, and consoleId = 1. The problem is that, it is not showing to the list. I've also added SNES to consoleModel having a consoleId = 5.

ngreenwood6 Over a year ago

Yeah, the code i provided you will not give you back the results if you do not have any games inside of the models. However, if you change the "INNER JOIN" to a "LEFT JOIN" then it will give you the consoles/models even if they do not have any games. It all depends on what you are trying to accomplish.

|

Dharmendra · Accepted Answer · 2013-02-07 07:17:51Z

3

Since you are using php function to encode json , it will give you the json in its default format. what you have to do is create your own function using string manipulation to get desired result.

answered Feb 7, 2013 at 7:17

Dharmendra

2161 silver badge14 bronze badges

4 Comments

Jahm Over a year ago

I'll be putting the result of string manipulation inside json_encode right, or not?

Dharmendra Over a year ago

no you don't need json_encode anywhere just some for loops and some string concatenations.

Jahm Over a year ago

But will the output still be JSON?

Dharmendra Over a year ago

json is nothign but a format of data having parent-child relations and can have multi layers of these relationships. And if you can get this structure through php or naything and save your file as .json extension then you got yourself a json file that's it.

Collectives™ on Stack Overflow

Multiple MySQL table to json_encode

2 Answers 2

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related