我需要从MySQL查询开始发送JSON中的多维数组。
SQL 查询是一个左联接,如下所示:
SELECT `TEAM`.`ID` AS TEAM_ID, `TEAM`.`NAME` AS TEAM_NAME, `TEAM_PLAYER`.`ID` AS TEAM_PLAYER_ID, `TEAM_PLAYER`.`NAME` AS TEAM_PLAYER_NAME FROM `TEAM_PLAYER`
LEFT JOIN `TEAM` ON `TEAM_PLAYER`.`TEAM_ID` = `TEAM`.`ID`
但是,此查询返回一个带有重复列的平面数组,例如
1 红袜子 34 约翰母鹿
1 红袜子 39 迈克尔·凯奇
2 天鹅绒 94 咏叹调山姆
通过互联网发送既多余又
麻烦。相反,我想获取一个 JSON,例如:
"0": {TEAM_ID : 1, TEAM_NAME : "RED SOCKS", "0": {TEAM_PLAYER_ID: 34, TEAM_PLAYER_NAME: JOHN DOE}, "1": {TEAM_PLAYER_ID: 39, TEAM_PLAYER_NAME: MICHAEL CAGE} },
"1": {TEAM_ID : 2, TEAM_NAME : "VELVET", "0": {TEAM_PLAYER_ID: 94, TEAM_PLAYER_NAME: ARIA SAM}
返回并调用 json_encode()
有没有适当的方法可以做到这一点?
我会在PHP中这样做。
基础知识:使用stdClass
对象创建快速易于转换的对象。 使用{}
表示法检查变量ids
。
count(get_object_vars($master->{$id}))
:这是最不寻常的部分。它计算当前选定属性 ( id
) 中的属性数量。两个属性是固定的:TEAM_ID
和TEAM_NAME
,因此我们可以从总数中减去这些属性,其他属性是团队合作者。返回的整数将为我们提供新团队成员的当前id
。
它被注释来解释,但是代码是非常不言自明的:
$array = array( array(1, "RED SOCKS", 34, "JOHN DOE"), array(1, "RED SOCKS", 39, "MICHAEL CAGE"), array(2, "VELVET", 94, "ARIA SAM") ); //demo
$master = new stdClass();
for ($i = 0; $i < count($array); $i++)
{
$id = (intval($array[$i][0])-1); //subtract 1 to make it zero based.
//check if the id is already present as object, if not create.
if (!isset($master->{$id}))
{
//set id;
$master->{$id} = new stdClass();
$master->{$id}->TEAM_ID = $id+1;
$master->{$id}->TEAM_NAME = $array[$i][1];
}
$currentKey = (count(get_object_vars($master->{$id}))) -2; //total key amount minus two fixed keys.
$master->{$id}->{$currentKey} = new stdClass();
$master->{$id}->{$currentKey}->TEAM_PLAYER_ID = $array[$i][2];
$master->{$id}->{$currentKey}->TEAM_PLAYER_NAME = $array[$i][3];
}
echo json_encode($master);
我的服务器上的结果:
{
"0":{"TEAM_ID":1,"TEAM_NAME":"RED SOCKS",
"0":{"TEAM_PLAYER_ID":34,"TEAM_PLAYER_NAME":"JOHN DOE"},
"1":{"TEAM_PLAYER_ID":39,"TEAM_PLAYER_NAME":"MICHAEL CAGE"}
},
"1":{"TEAM_ID":2,"TEAM_NAME":"VELVET",
"0":{"TEAM_PLAYER_ID":94,"TEAM_PLAYER_NAME":"ARIA SAM"}
}
}