php - 查询 MySQL 表返回 JSON

Question

我有一个包含温度传感器数据的 MySQL 表，我正在尝试将其数据放入 JSON 数组中，以便使用 JavaScript 图表输出到 PHP 页面。

这是我的表格和一些示例数据:

+----+----------+---------------------+-------+
| id | sensorId |    dateRecorded     | tempF |
+----+----------+---------------------+-------+
|  1 |        1 | 2018-03-31 10:00:00 |  34.2 |
|  2 |        2 | 2018-03-31 10:00:00 |  83.1 |
|  3 |        2 | 2018-03-31 10:05:00 |  44.5 |
|  4 |        1 | 2018-03-31 10:05:00 |  65.2 |
+----+----------+---------------------+-------+

我试图获取的 JSON 结构作为输出是这样的:

data: [
    { dateRecorded: '2018-03-31 10:00:00', sensor1: 34.2, sensor2: 83.1 },
    { dateRecorded: '2018-03-31 10:05:00', sensor1: 65.2, sensor2: 44.5 }
]

我可以单独返回每个传感器的数据，但我似乎无法弄清楚如何按 dateRecorded 列对输出进行分组，然后将每个传感器的数据放入其自己的键/值对中？

Answer 1

您的 JSON 输出结构不是有效的 JSON。

data: [
    { dateRecorded: '2018-03-31 10:00:00', sensor1: 34.2, sensor2: 83.1 },
    { dateRecorded: '2018-03-31 10:05:00', sensor1: 65.2, sensor2: 44.5 }
]

这是一个有效的 JSON 结构，因此这就是我的答案的目标。

{
    "data": [{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }]
}

这只能通过纯 MySQL 实现。

创建表/插入数据

CREATE TABLE Table1
    (`id` int, `sensorId` int, `dateRecorded`datetime, `tempF` double)
;

INSERT INTO Table1
    (`id`, `sensorId`, `dateRecorded`, `tempF`)
VALUES
    (1, 1, '2018-03-31 10:00:00', 34.2),
    (2, 2, '2018-03-31 10:00:00', 83.1),
    (3, 2, '2018-03-31 10:05:00', 44.5),
    (4, 1, '2018-03-31 10:05:00', 65.2)
;

使用 MySQL 生成内部 JSON 结构，如

{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }

您需要使用函数 CONCAT 和 GROUP_CONCAT 来生成 JSON 字符串。

查询

SELECT 
   CONCAT (
       '{'
     , '"dateRecorded": ', '"', Table1.dateRecorded, '"'
     , ','
     , GROUP_CONCAT(     
         CONCAT(
           '"sensor', Table1.sensorId, '":', Table1.tempF
         )
         ORDER BY
          Table1.id ASC
       )
     , '}'
   )
    AS json_data_records
FROM 
 Table1 
GROUP BY
 Table1.dateRecorded
ORDER BY 
 Table1.dateRecorded ASC

结果

|                                                     json_data_records |
|-----------------------------------------------------------------------|
| {"dateRecorded": "2018-03-31 10:00:00","sensor1":34.2,"sensor2":83.1} |
| {"dateRecorded": "2018-03-31 10:05:00","sensor2":44.5,"sensor1":65.2} |

查看演示 http://www.sqlfiddle.com/#!9/d6db452/6

p.s 第二条记录中sensor1和sensor2的顺序颠倒了。
因为在 GROUP_CONCAT 函数中关闭了 ORDER BY id ASC。
除此之外，此顺序存在于源数据中。

生成完整的 JSON，如

{
    "data": [{
        "dateRecorded": "2018-03-31 10:00:00",
        "sensor1": 34.2,
        "sensor2": 83.1
    }, {
        "dateRecorded": "2018-03-31 10:05:00",
        "sensor1": 65.2,
        "sensor2": 44.5
    }]
}

我们需要更改现有查询，以便它使用 CONCAT 和 GROUP_CONCAT 来合并最后的输出。

查询

SELECT 
 CONCAT(
    '{'
      , '"data": ['
          , GROUP_CONCAT(json_records.json)
     , ']'
   , '}'
 )
  AS json
FROM ( 
  SELECT
     CONCAT (
         '{'
       , '"dateRecorded": ', '"', Table1.dateRecorded, '"'
       , ','
       , GROUP_CONCAT(     
           CONCAT(
             '"sensor', Table1.sensorId, '":', Table1.tempF
           )
           ORDER BY
            Table1.id ASC
         )
       , '}'
     ) 
      AS json
  FROM 
   Table1 
  GROUP BY
   Table1.dateRecorded
  ORDER BY 
   Table1.dateRecorded ASC
) 
 AS json_records

结果

|                                                                                                                                                    json |
|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| {"data": [{"dateRecorded": "2018-03-31 10:00:00","sensor1":34.2,"sensor2":83.1},{"dateRecorded": "2018-03-31 10:05:00","sensor2":44.5,"sensor1":65.2}]} |

查看演示 http://www.sqlfiddle.com/#!9/d6db452/19