时间戳

首页 > 解决方案 > 如何通过匹配日期对 mysqli_fetch_array 结果进行分组？

php - 如何通过匹配日期对 mysqli_fetch_array 结果进行分组？

问题描述

我正在尝试使用 php/mysql 在 morrisjs 中构建图形，并且需要输出具有以下格式的对象。它应该对期间日期进行分组，然后列出其后面的名称以匹配结果：

{period: "2019-02-06 12:00:00", shelly: 2147483647, debra: 1240996571, sally: 2147483647, bill: 2147483647, bob: 619685085, jim: 126614618},
{period: "2019-02-06 12:30:00", shelly: 2147483647, debra: 1240996571, sally: 2147483647, bill: 2147483647, bob: 619685085, jim: 126614618},
{period: "2019-02-06 13:00:00", shelly: 2147483647, debra: 1240996571, sally: 2147483647, bill: 2147483647, bob: 619685085, jim: 126614618}

PHP：

    while ($row = mysqli_fetch_array($poolChart)) {
        $chart_data .= "{ \"period\": \"".$row["StatsHistoryTime"]."\", \"name\": \"".$row["name"]."\", \"hashrate\":".$row["hashrate"]."}, ";

    }
    $chart_data = substr($chart_data, 0, -2);

目前我的输出看起来像这样

[
  {
    "period": "2019-02-06 12:00:00",
    "name": "shelly",
    "hashrate": 2147483647
  },
  {
    "period": "2019-02-06 12:00:00",
    "name": "debra",
    "hashrate": 1240996571
  },
  {
    "period": "2019-02-06 12:00:00",
    "name": "sally",
    "hashrate": 2147483647
  },
  {
    "period": "2019-02-06 12:00:00",
    "name": "bill",
    "hashrate": 2147483647
  }
  {
    "period": "2019-02-06 12:30:00",
    "name": "shelly",
    "hashrate": 2147483647
  },
  {
    "period": "2019-02-06 12:30:00",
    "name": "debra",
    "hashrate": 1460613388
  },
  {
    "period": "2019-02-06 12:30:00",
    "name": "sally",
    "hashrate": 2147483647
  },
  {
    "period": "2019-02-06 12:30:00",
    "name": "bill",
    "hashrate": 2147483647
  }
]

标签： phpmysqlmysqli-fetch-array

解决方案

解决问题的方法是将数据转换为适当的格式（每个周期值的数组，数组中的其他值是每个name和的组合hashrate），然后您可以使用json_encode将其转换为所需的格式：

$chartdata = array();
while ($row = mysqli_fetch_array($poolChart)) {
    $period = $row['StatsHistoryTime'];
    // have we already got data for this period?
    if (($k = array_search($period, array_column($chartdata, 'period'))) !== false) {
        // yes, update data for this period
        $chartdata[$k][$row['name']] = $row['hashrate'];
    }
    else {
        // no, create a new array
        $chartdata[] = array('period' => $period, $row['name'] => $row['hashrate']);
    }
}
echo json_encode($chartdata);

输出（用于您的样本数据）

[
  {
    "period":"2019-02-06 12:00:00",
    "shelly":2147483647,
    "debra":1240996571,
    "sally":2147483647,
    "bill":2147483647
  },
  {
    "period":"2019-02-06 12:30:00",
    "shelly":2147483647,
    "debra":1460613388,
    "sally":2147483647,
    "bill":2147483647
  }
]

3v4l.org 上的演示

推荐阅读