如何在MySql中从两个表构建JSON文件
如何在MySql中从两个表构建JSON文件
提问于 2018-07-08 23:52:26
我有两个由两列相互关联的表，我需要从表2中获取数据，以便在JSON文件中完成数组。
表1
id | time | ruta | destino |
------------------------------
1 | 1hr | M17 | Town |
2 | 24m | M11 | Town |表2
id | ref | ruta | destino |
------------------------------
1 | A01 | M17 | Town |
2 | A08 | M17 | Town |
3 | A09 | M17 | Town |
4 | A11 | M17 | Town |
5 | A41 | M11 | Town |
6 | A15 | M11 | Town |我需要从表2中选择与ruta和相同destino相关的ref列的所有数据，因为它在JSON文件中按如下顺序显示："ref":"A01, A08, A09, A11"
[{
"id":"1",
"ruta":"M17",
"destino":"Town",
"ref":"A01, A08, A09, A11",
},{
"id":"2",
"ruta":"M11",
"destino":"Town",
"ref":"A41, A15",
},我的问题是这个$query = mysqli_query($con, "SELECT * FROM table1");，但我不知道我做了，我是新手在php，谢谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-07-09 00:01:51
如果您需要来自这两个表的结果，则可以将join和group_concat与group by一起使用
"SELECT t1.id, t1.ruta, t1.destino, group_concat(t2.ref) as ref
FROM table1 t1
INNER JOIN table2 t2 on t1.ruta = t2.ruta and t1.destino = t2.destino
GROUP BY t1.id, t1.ruta, t1.destino "Stack Overflow用户
发布于 2018-07-09 01:47:53
请考虑以下几点：
<?php
/*
DROP TABLE IF EXISTS t1;
CREATE TABLE t1
( id SERIAL PRIMARY KEY
, ruta CHAR(3) NOT NULL
);
INSERT INTO t1 VALUES
(1,'M17'),
(2,'M11');
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
(id SERIAL PRIMARY KEY
,ref CHAR(3) NOT NULL
,ruta CHAR(3) NOT NULL
);
INSERT INTO t2 VALUES
(1,'A01','M17'),
(2,'A08','M17'),
(3,'A09','M17'),
(4,'A11','M17'),
(5,'A41','M11'),
(6,'A15','M11');
SELECT t1.*
, t2.ref
FROM t1
JOIN t2
ON t2.ruta = t1.ruta
ORDER
BY t1.id
, t2.id;
+----+------+-----+
| id | ruta | ref |
+----+------+-----+
| 1 | M17 | A01 |
| 1 | M17 | A08 |
| 1 | M17 | A09 |
| 1 | M17 | A11 |
| 2 | M11 | A41 |
| 2 | M11 | A15 |
+----+------+-----+
*/
require('path/to/connection/stateme.nts');
$query = "
SELECT t1.id
, t1.ruta
, t2.ref
FROM t1
JOIN t2
ON t2.ruta = t1.ruta
ORDER
BY t1.id
, t2.id;
";
$result = mysqli_query($conn,$query);
$array = array();
while($row = mysqli_fetch_assoc($result)){
$array[] = $row;
}
$new_array = array();
foreach($array as $row){
$new_array[$row['id']]['id'] = $row['ruta'];
$new_array[$row['id']]['ruta'][] = $row['ref'];
}
$json = json_encode($new_array);
echo($json);
/*
Outputs: {"1":{"id":"M17","ruta":["A01","A08","A09","A11"]},"2":{"id":"M11","ruta":["A41","A15"]}}
or something like that
*/
?>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51233691
复制相关文章
相似问题