我有一个JSON数组如下:
{
"aaData": [
{
"Date_time": "23",
"traffic": "22",
"direction": "sent"
},
{
"Date_time": "24",
"traffic": "55",
"direction": "sent"
},
{
"Date_time": "25",
"traffic": "60",
"direction": "sent"
},
{
"Date_time": "26",
"traffic": "43",
"direction": "sent"
},
{
"Date_time": "27",
"traffic": "50",
"direction": "sent"
},
{
"Date_time": "23",
"traffic": "50",
"direction": "received"
},
{
"Date_time": "24",
"traffic": "42",
"direction": "received"
},
{
"Date_time": "25",
"traffic": "52",
"direction": "received"
},
{
"Date_time": "26",
"traffic": "47",
"direction": "received"
},
{
"Date_time": "27",
"traffic": "36",
"direction": "received"
}
]
}
我想做的是将所有具有相同日期的结果合并到一个单独的条目中-所以对于date_time 23,我希望它看起来像这样
"Date_time": "23",
"traffic-sent": "22",
"traffic-received": "50"
如果可能的话,我想用PHP做这个?数据来自两个独立的mySQL查询,来自不同的mySQL数据库。我已经尝试组合查询的输出来做我需要的(尝试连接和联合),但不能像我的第一个例子那样得到结果的分离。
创建JSON的SQL查询部分如下:while($row = mysqli_fetch_assoc($result)) {
$model[$i]['Date_time'] = $row['the_day'];
$model[$i]['traffic'] = $row['traffic'];
$model[$i]['direction'] = $row['TABLE_NAME'];
$i++;
}
和SQL看起来像这样:
(SELECT
DAY(`Time`) AS the_day,
count(accounts.accName) AS traffic,
"sent" AS TABLE_NAME
FROM
bss.ss_sent LEFT JOIN bss.accounts ON ss_sent.Customer = accounts.accName
WHERE
YEARWEEK(`Time`) = YEARWEEK(CURRENT_DATE)
AND
Customer != " "
AND
accShortName = "QRR"
GROUP BY
the_day)
UNION
(SELECT
DAY(Date_time) AS the_day,
count(AS_Task) AS traffic,
"received" AS TABLE_NAME
FROM
im_stats.as_counter
WHERE
AS_Task = "QRR3 Incoming"
AND
YEARWEEK(Date_time) = YEARWEEK(CURRENT_DATE)
GROUP BY
the_day
Order by the_day)
如果有人能建议一种方法来结合这些结果,我将非常感激。
更新:我是这样输入Populus的代码的:
$i = 0;
while($row = mysqli_fetch_assoc($result)) {
$model[$i]['Date_time'] = $row['the_day'];
$model[$i]['traffic'] = $row['traffic'];
$model[$i]['direction'] = $row['TABLE_NAME'];
$i++;
}
$combined = array();
foreach ($model as $val) {
$date_time = $val['Date_time'];
if (!isset($combined[$date_time)) {
$combined[$date_time] = array(
'Date_time' => $date_time,
'traffic_sent' => 0,
'traffic_received' => 0,
);
}
if ('received' == $val['direction']) {
$combined[$date_time]['traffic_received'] += $val['traffic'];
} else {
$combined[$date_time]['traffic_sent'] += $val['traffic'];
}
}
header('Content-type: application/json');
print json_encode(array('aaData' => $combined), JSON_PRETTY_PRINT);
这可能可以使用SQL完成(您还没有提供),但如果您真的需要PHP:
$combined = array();
foreach ($model as $val) {
$date_time = $val['Date_time'];
if (!isset($combined[$date_time])) {
$combined[$date_time] = array(
'Date_time' => $date_time,
'traffic_sent' => 0,
'traffic_received' => 0,
);
}
if ('received' == $val['direction']) {
$combined[$date_time]['traffic_received'] += $val['traffic'];
} else {
$combined[$date_time]['traffic_sent'] += $val['traffic'];
}
}
您想要的数组现在在$combined
中。如果你不想要这些键,你可以删除它:
$result = array_values($combined);
试试这样做:
while($row = mysqli_fetch_assoc($result)) {
if ($row['direction'] == 'sent')
$dt[$row['Date_time']]['traffic-sent'] += $row['traffic'];
elseif ($row['direction'] == 'recieved')
$dt[$row['Date_time']]['traffic-recieved'] += $row['traffic'];
}
foreach ($dt as $date) {
echo "Date_time: " . key($date) . ",<br/>" .
"traffic_sent: " . $date['traffic-sent'] . ",<br/>" .
"traffic-recieved: " . $date['traffic-recieved'] . "<br/><br/>";
}