我在本地服务器上用PHPMyAdmin创建了一个MysqL数据库.在这个数据库中,我存储了我的朋友的名字和最喜欢的NBA球队.这显然是一个多对多的关系.出于这个原因,我在
MySQL中运行以下脚本来为该数据库创建适当的表：
@H_301_1@CREATE TABLE `friends` (
`id` int(4) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)
)
CREATE TABLE `teams` (
`id` int(4) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)
)
CREATE TABLE `relations` (
`friends_id` int(4) NOT NULL,`teams_id` int(4) NOT NULL,)
显然,我在这些表中插入了一些值,但是我没有在这里提供大量的源代码以节省一些空间.其中一小部分如下：
@H_301_1@INSERT INTO `friends` (`id`,`name`) VALUES (1,'David Belton'),(2,'Alex James'); INSERT INTO `teams` (`id`,'Cleveland Cavaliers'),'Boston Celtics'); INSERT INTO `relations` (`friends_id`,`teams_id`) VALUES (1,1),2);在运行从数据库中获取数据并打印它们的PHP脚本之后,我希望为每个朋友提供以下类型的有效json输出：
@H_301_1@{ "id": "1","name": "Alex James","team": ["Boston Celtics","Cleveland Cavaliers"] }如何为每个拥有MysqL的人制作这个最喜欢的球队？
“eazy”方法是使用CONCAT生成JSON.
并使用GROUP_CONCAT将多个团队记录合并为一个JSON数组.
此方法也适用于不支持创建JSON函数的旧MysqL版本.
原文链接：https://www.f2er.com/php/138922.html并使用GROUP_CONCAT将多个团队记录合并为一个JSON数组.
此方法也适用于不支持创建JSON函数的旧MysqL版本.
询问
@H_301_1@SELECT CONCAT( "{",'"id"',":",'"',friends.id,",'"name"',friends.name,'"team"',"[",GROUP_CONCAT('"',teams.name,'"'),"]","}" ) AS json FROM friends INNER JOIN relations ON friends.id = relations.friends_id INNER JOIN teams ON relations.teams_id = teams.id WHERE friends.id = 1结果
@H_301_1@| json | |-----------------------------------------------------------------| | {"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]} |演示
http://www.sqlfiddle.com/#!9/4cd244/19
编辑了更多的朋友
询问
@H_301_1@SELECT CONCAT( "[",GROUP_CONCAT(json_records.json) # combine json records into a string,"]" ) AS json FROM ( SELECT CONCAT( "{","}" ) AS json FROM friends INNER JOIN relations ON friends.id = relations.friends_id INNER JOIN teams ON relations.teams_id = teams.id WHERE friends.id IN(SELECT id FROM friends) #select the friends you need or just simply friends.id IN(1,2) GROUP BY friends.id ) AS json_records结果
@H_301_1@| json | |--------------------------------------------------------------------------------------------------------------------------------------------------| | [{"id":"1","team":["Cleveland Cavaliers"]},{"id":"2","name":"Alex James","team":["Boston Celtics","Cleveland Cavaliers"]}] |演示
