PHP从mysql的结果设置为系列的图表 - PHP from mysql results set to series for charts?

想象每一行是X轴，每一列是一个系列(图表中的一条线):

---------------------------------
year | apple_price | banana_price
---------------------------------
2010   2.5           1.7
2011   2.6           2.0
array // MySQL results set
  0 => 
    array
      'year' => 2010
      'apple_price' => 2.5
      'banana_price' => 1.7
  1 => 
    array
      'year' => 2011
      'apple_price' => 2.6
      'banana_price' => 2.0

我需要转换mysql数组的东西更适合我的图表API和JSON。你将如何实现这一点?有可能对结果集进行转置吗?编辑: 这当然应该是动态的:第一列是X轴，剩下的将是系列(图表中的线条):

array // indexed
  0 => 
    array // associative
      'name' => 'apple_price'
      'data' => [[2010, 2.5], [2011, 2.6]] // indexed array of array
  1 => 
    array // associative
      'name' => 'banana_price'
      'data' => [[2010, 1.7], [2011, 2.0]] // indexed array of array

EDIT:第一个快速和肮脏的工作解决方案，试图得到一个更好的:

public static function mysqlResultSetToChartSeries($data)
{
    // Return empty array if 0 rows or columns < 2
    if (!isset($data[0]) || !count($data[0]) > 1) return array();
    // Get all keys (columns names)
    $keys = array_keys($data[0]);
    // Temp array for storing col names and values
    $tmp = array();
    foreach($keys as $k) $tmp[$k] = array_map(function($r) use ($k){
        return $r[$k];
    }, $data);
    // X axis
    $x = array_shift($tmp);
    $series = array_map(function($k, $v) use ($x) {
        return array(
            'name' => $k,
            'data' => array_map(function($xaxis, $yaxis) use ($x) {
                return array($xaxis, $yaxis);
            }, array_values($x), $v)
        );
    }, array_keys($tmp), array_values($tmp));
    return $series;
}

类别:

<?php
class ChartData {
    private $data;
    private $columns;
    private $xAxis;
    /**
     * @param string $xAxis The key of the array that is the x-axis.
     * @param array $columns An array of column names.
     */
    public function __construct($xAxis, array $columns) {
            $this->data = array();
        $this->xAxis = $xAxis;
        //we need to build an index to know how to get
        //from a key to an index.
        $i = 0;
        foreach ($columns as &$key) {
            if ($key != $xAxis) {
                $this->columns[$key] = $i++;
            }
        }
        foreach ($this->columns as $key => &$value) {
            $this->data[$value] = array(
                'name' => $key,
                'data' => array()
            );
        }
    }
    /**
     * @param array $data An associative array mapping data names to values.  Must include the index 'year' and otherwise match $columns
         */
    public function add(array $data) {
        foreach ($data as $key => &$value) {
            if ($key != $this->xAxis) {
                $this->data[$this->columns[$key]]['data'][] = array($data[$this->xAxis], $value);
            }
        }
    }
    /**
     * @return array An associative array in the format for the Charts API.
     */
    public function getArray() {
        return $this->data;
    }
}
?>

使用

$pricesData = array(
    array(
        'year' => '2010',
        'jamba' => '2.5',
        'juice' => '1.7'
    ),
    array(
        'year' => '2011',
        'jamba' => '2.6',
        'juice' => '2.0'
    )
);
$prices = new ChartData('year', array('year', 'jamba', 'juice'));
foreach ($pricesData as &$priceData) {
    $prices->add($priceData);
}
print_r($prices->getArray());

指出:

我的实现需要你告诉它x轴将是什么。在构造函数
动态。你可以有尽可能多的列，因为你想…
它被设计为一次创建一个记录。这意味着你要么:
- 传递数据库获取循环中的每个记录。(推荐用于性能)
- 循环遍历预构建的数组并传递每个值。(示例显示，不是很有效，很好地解耦了类)