大佬教程收集整理的这篇文章主要介绍了php – 如何使用3个多对多关系表从MySQL连接数组,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
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,)
显然,我在这些表中插入了一些值,但是我没有在这里提供大量的源代码以节省一些空间.其中一小部分如下:
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);
在运行从数据库中获取数据并打印它们的@L_673_0@脚本之后,我希望为每个朋友提供以下类型的有效json输出:
{ "id": "1","name": "Alex James","team": ["Boston Celtics","Cleveland Cavaliers"] }
如何为每个拥有MysqL的人制作这个最喜欢的球队?
询问
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
结果
| json | |-----------------------------------------------------------------| | {"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]} |
演示
http://www.sqlfiddle.com/#!9/4cd244/19
编辑了更多的朋友
询问
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
结果
| json | |--------------------------------------------------------------------------------------------------------------------------------------------------| | [{"id":"1","team":["Cleveland Cavaliers"]},{"id":"2","name":"Alex James","team":["Boston Celtics","Cleveland Cavaliers"]}] |
演示
以上是大佬教程为你收集整理的php – 如何使用3个多对多关系表从MySQL连接数组全部内容,希望文章能够帮你解决php – 如何使用3个多对多关系表从MySQL连接数组所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。