我正在尝试将MySQL请求解析为JSON文件,使用此PHP函数,
<?php
//Define possible Argument request
$format = $_GET['format'];
if($format=='json') {
header("Content-type: text/json");
}
//Define database credential
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
try {
//Open connection to mysql_db from defined Database credentials
$connection = mysqli_connect($servername, $username, $password, $dbname) or die ("Error " . mysqli_error($connection));
//Fetch table rows from mysql db
$sql = "SELECT TS, time1, time2, time3 FROM time ORDER BY TS";
$result = mysqli_query($connection, $sql) or die ("Error in Selecting " . mysqli_error($connection));
//Create an array
$series0 = array();
$series0['name'] = 'TS';
$series1 = array();
$series1['name'] = 'time1';
$series2 = array();
$series2['name'] = 'time2';
$series3 = array();
$series3['name'] = 'time3';
while($r = mysqli_fetch_array($result))
{
$series0['data'][] = $r['TS'];
$series1['data'][] = $r['time1'];
$series2['data'][] = $r['time2'];
$series3['data'][] = $r['timez3'];
}
$result = array();
array_push($result,$series0);
array_push($result,$series1);
array_push($result,$series2);
array_push($result,$series3);
print json_encode($result, JSON_NUMERIC_CHECK);
mysqli_close($connection);
}
catch(PDOException $e) {
echo $e->getMessage();
}
?>
谁给了我这个 JSON 输出:
[{
"name": "TS",
"data": ["00:08:31.227","00:09:02.434","00:09:38.573"]
},{
"name":"time1",
"data":["00:05:11.220","00:05:05.420","00:03:32.540"]
},{
"name":"time2",
"data":["00:04:11.820","00:08:05.660","00:01:24.330"]
},{
"name":"time3",
"data":["00:02:11.990","00:09:05.570","00:15:25.200"]
}]
现在的问题是我必须将时间从"HH:MM:SS.fff"转换为秒(SS.fff),但是尝试应用我的转换函数时我遇到了一个错误,因为我无法将我的公式播放到数组中,有没有办法拦截数据并在它们进入数组之前对其进行操作?
第二个小问题,因为最终数组将获得 100+ 个数据系列,有没有办法不手动为系列分配名称,而是给它们与 MySQL 表列相同的名称来自哪里?
感谢您的所有建议,此致敬意。
您可以使用
mysql TIME_TO_SEC函数。
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
所以改变
$sql = "SELECT TS, time1, time2, time3 FROM time ORDER BY TS";
自
$sql = "SELECT TS, TIME_TO_SEC(time1), TIME_TO_SEC(time2), TIME_TO_SEC(time3) FROM time ORDER BY TS";