如何将数据从MySQL表放入谷歌图表API?

I'm struggling to work out how to make a chart out of the data I have, heres an example.

我正在努力研究如何从我所拥有的数据中做一个图表，这是一个例子。

My Chart with dummy data should look like this:

我的虚拟数据图表应该是这样的:

function drawChart() {
    var data = google.visualization.arrayToDataTable([
    ['Month', 'Margarita Murphy', 'Lora Gonzales', 'Mario Moran', 'Wefico Local Faire', 'Zegko Collection', 'Saxux Program for Youth', 'Test New location venue'],
    ['4/12', 9, 74, 10, 8, 93, 33, 90], 
    ['5/12', 10, 168, 0, 10, 198, 108, 154], 
    ['6/12', 9, 174, 12, 12, 165, 96, 261], 
    ['7/12', 12, 288, 8, 36, 180, 264, 140], 
    ['8/12', 40, 275, 15, 30, 275, 395, 170], 
    ['9/12', 54, 534, 30, 48, 240, 246, 552], 
    ['10/12', 28, 518, 63, 28, 182, 672, 98], 
    ['11/12', 56, 520, 8, 64, 424, 568, 704], 
    ['12/12', 45, 675, 9, 63, 864, 567, 756], 
    ['1/13', 90, 570, 40, 70, 350, 510, 150], 
    ['2/13', 55, 946, 110, 55, 253, 429, 88], 
    ['3/13', 96, 684, 12, 96, 528, 1140, 468], 
    ['4/13', 52, 832, 104, 130, 1261, 1235, 663], 
    ['5/13', 28, 756, 70, 70, 1050, 910, 728], 
    ['6/13', 105, 930, 15, 60, 1440, 660, 690], 
    ['7/13', 144, 1600, 96, 64, 1312, 1488, 1120], 
]);

So as you can see it has a list of items, with how many views it has had per month going back in time.

你可以看到它有一个项目列表，每个月有多少个视图返回。

The problem I am having is when I get the data from MySQL I want to loop though the views, which would be going vertically down in the column, instead of accross. How would I go about making it go horizontal like so:

我遇到的问题是，当我从MySQL中获取数据时，我想要循环视图，而视图是垂直向下的，而不是accross。我怎么让它像这样横向移动呢

['4/12', item1.views, item2.view]
['5/12', item1.views, item2.view]

I'm getting really confused by this...

我真的很困惑。

Example Data

示例数据

-------------------
|date|views|ref_id|
|----|-----|------|
|4/12|123  |2     |
|5/12|526  |7     |
|6/12|2    |1     |
|7/12|46   |3     |
-------------------

EDIT:

编辑:

Playing around with setting the dates as variables first and then looping though everything and adding the data to the correct one?

先将日期设置为变量，然后遍历所有内容，然后将数据添加到正确的变量中?

$month4_12 = "['4/12', ";
$month5_12 = "['5/12', ";

foreach($views_data as $data){
    ${"month_$data->date"} .= $data->views . ', ';
}

$month4_12 .= "],";
$month5_12 .= "],";

EDIT2:

EDIT2:

So here's what I have now, it has a few problems though, if the views table doesn't contain a record, it doesn't count as it only goes off what it finds in the database... It no obviously doesn't work as it doesn't have the correct amount of columns compared to titles.

这是我现在所拥有的，它有一些问题，如果视图表不包含记录，它不会被计数，因为它只会在数据库中找到。它没有明显的不工作，因为它没有正确的数量的列与标题。

// Get views for chart
$views_data = $this->content_model->get_chart_view_data();

// First make the months
$month = 1;
while($month <= 16){
    $month_text = date('d/m/y');
    $month_text = strtotime($month_text . ' -'.$month.' months');
    $month_text_display = date('n/y', $month_text);
    $month_text_variable = str_replace('/', '_', $month_text_display);
    ${"month_$month_text_variable"} = "['".$month_text_display."', ";

    // Now add the data
    foreach($views_data as $row){
        ${"month_$month_text_variable"} .= $row->views . ', ';
    }
    ${"month_$month_text_variable"} = rtrim(${"month_$month_text_variable"}, ", ");

    // Finish the lines
    ${"month_$month_text_variable"} .= "],\n";

    $month++;
}

// Now join the lot!
$month = 1;
$chart_data = '';
while($month <= 16){
    $month_text = date('d/m/y');
    $month_text = strtotime($month_text . ' -'.$month.' months');
    $month_text_display = date('n/y', $month_text);
    $month_text_variable = str_replace('/', '_', $month_text_display);
    $chart_data .= ${"month_$month_text_variable"};
    $month++;
}

$data['chart_data'] = rtrim($chart_data, ",\n");

echo $data['chart_data'];

This gives the output:

这使输出:

function drawChart() {
        var data = google.visualization.arrayToDataTable([
        ['Month', 'Margarita Murphy', 'Lora Gonzales', 'Mario Moran', 'Wefico Local Faire', 'Zegko Collection', 'Saxux Program for Youth', 'Test New location venue'],
        ['7/13', 2, 1, 1],
        ['6/13', 2, 1, 1],
        ['5/13', 2, 1, 1],
        ['4/13', 2, 1, 1],
        ['3/13', 2, 1, 1],
        ['2/13', 2, 1, 1],
        ['1/13', 2, 1, 1],
        ['12/12', 2, 1, 1],
        ['11/12', 2, 1, 1],
        ['10/12', 2, 1, 1],
        ['9/12', 2, 1, 1],
        ['8/12', 2, 1, 1],
        ['7/12', 2, 1, 1],
        ['6/12', 2, 1, 1],
        ['5/12', 2, 1, 1],
        ['4/12', 2, 1, 1]
]);

EDIT 3

编辑3

Heres how the views data is stored in the database, you can see that a day with no views simply has no record

Heres如何将视图数据存储在数据库中，您可以看到没有视图的一天根本没有记录。

1 个解决方案

#1

You need to pivot the data in your SQL query. Since MySQL doesn't support pivots natively, you have to cheat a bit. Each pivoted column in your final output will be in this form:

您需要将数据转到SQL查询中。由于MySQL不支持主点，所以您必须稍微作弊。最终输出中的每一列都将以这种形式出现:

IF(ref_id = <this column's reference id>, views, 0) AS <column name>

and then you group by the date column, like this:

然后你按日期列，像这样:

SELECT
    data,
    IF(ref_id = 327, views, 0) AS column_327,
    IF(ref_id = 329, views, 0) AS column_329,
    // etc...
FROM <table name>
WHERE <conditions>
GROUP BY date

Then you can iterate over the output to build a DataTable object.

然后，您可以遍历输出来构建一个DataTable对象。

If you don't know all of the ref_id values ahead of time (or there are a lot of them), then you can query to get a list of ref_id's and build the query programmatically:

如果您事先不知道所有ref_id值(或者有很多这样的值)，那么您可以查询获取ref_id的列表并以编程方式构建查询:

SELECT DISTINCT ref_id FROM <table name>