我在本地服务器上用PHPMyAdmin创建了一个MysqL数据库.在这个数据库中,我存储了我的朋友的名字和最喜欢的NBA球队.这显然是一个多对多的关系.出于这个原因,我在
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);
在运行从数据库中获取数据并打印它们的PHP脚本之后,我希望为每个朋友提供以下类型的有效json输出：
{
"id": "1","name": "Alex James","team": ["Boston Celtics","Cleveland Cavaliers"]
}
如何为每个拥有MysqL的人制作这个最喜欢的球队？
附：
我预先假设在使用PHP检索数据之前,最好在MysqL中完成.
“eazy”方法是使用CONCAT生成JSON.
并使用GROUP_CONCAT将多个团队记录合并为一个JSON数组.
此方法也适用于不支持创建JSON函数的旧MysqL版本.
并使用GROUP_CONCAT将多个团队记录合并为一个JSON数组.
此方法也适用于不支持创建JSON函数的旧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"]}] |
演示
http://www.sqlfiddle.com/#!9/4cd244/61