将多个MySQL表转换为json_encode [英] Multiple MySQL table to json_encode
问题描述
我的数据库中有3个不同的表，分别称为consoleConsole，consoleModel和consoleGame.然后，我想做的就是每个控制台在其模型内部都有一个循环，而每个模型在其游戏中都有另一个循环，如下所示:
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"
}
]
}
]
}
]
但是可悲的是，我没有将数据库与此数据库一起使用，而是直接将其写在一个php文件中.
But sadly, I was not using my database with this one but instead I just wrote it straight in a php file.
编辑
无论如何，继续前进.我已经修改了代码，并最终像这样结束了.
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"
}
]
}
]
他们的关系:
我现在的问题是，我无法添加每个游戏的标题.
What my problem is right now, I can't add the Title of the each Game.
推荐答案
好的，所以我已经写下了您的解决方案.您必须确保其中包含了order by，因为它假定您正在一起订购其中的物品.我也不知道您的发布者是如何存储的，因此我将其分离到一个单独的表中(这将使您也可以从该发布者那里获取项目)，现在是4个联接.另外，我还对它进行了更新，使其也可以进行内部联接.这样，对于未分配任何游戏的游戏机，您将不会得到空的结果.如果需要这些，您可以简单地更改联接，以便它也能为您提供这些结果.让我知道这是否有帮助
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);
这篇关于将多个MySQL表转换为json_encode的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
