php - 如何在php中划分系列以创建用于谷歌图表的json表

问题描述

我必须使用数据库中的数据绘制许多曲线。数据库结构是这样的：

我想要的结果是这样的（在这种情况下只有2个系列和对数轴）

使用测试（字符串）作为系列名称（在图例中）和直径并作为 X 和 Y 值传递。我尝试了很多解决方案，但没有；我想我明白我需要拆分结果以进行测试（字符串），但我不知道如何。你能帮助我吗？

<?php
//connection
$connect = mysqli_connect("server","user","pw","DB");
//query
$result = mysqli_query($connect, "SELECT DISTINCT test, diameter, pass FROM 
tablex;");

// json C
$rows = array();
$table = array();
$array['cols'][] = array('label' => 'test', 'type' => 'strimg');
$array['cols'][] = array('label' => 'diameter', 'type' => 'number');
$array['cols'][] = array('label' => 'pass', 'type' => 'number');

// json R
while($row = mysqli_fetch_array($result))
{
 $provino = array ($row["test"]);
 $sub_array =  array('f' =>$row["test"]);
 $diameter = array ($row["diameter"]);
 $sub_array[] =  array('v' => $row["diameter"]);
 $pass = array ($row["pass"]);
 $sub_array[] =  array('v' => $row["pass"]);

 $rows []=  array('c' => $sub_array);
}
$array['rows'] = $rows;
$jsonTable = json_encode($array);
?>


<html>
 <head>
  <script type="text/javascript" 
src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript" 
src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
  <script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart()
{
 var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

 var Options = {

    title: 'CG',
    legend: 'bottom',
    width: 1000,
    height: 1000    
      };


var chart = new 
google.visualization.LineChart(document.getElementById('line_chart'));

chart.draw(data, Options);
}
</script>
<style>
</style>
</style>
</head>  
<body>
<div class="page-wrapper">
<br />
<div id="line_chart" style="width: 100%; height: 500px"></div>
</div>
</body>
</html>

标签： phpjsonmysqlichartsgoogle-visualization

解决方案

google折线图的数据格式
，要求x轴为数据表的第一列。
每个额外的列都被视为一个系列。

为了每个测试（字符串）都有一个系列，
数据表需要采用以下格式。

diameter  |  AAAAAAAAA  |  BBBBBBBBB  |  CCCCCCCCC
--------  |  ---------  |  ---------  |  ---------
20        |  12         |             |           
10        |  19         |             |           
7         |  72         |             |           
21        |             |  15         |           
12        |             |  52         |           
3         |             |  65         |           
19        |             |             |  44       
11        |             |             |  22        
5         |             |             |  36

这可能很难在您的查询/php中构建，
特别是如果有更多的测试系列（字符串）。

因此，您可以单独使用 php，
并使用 google 的DataView类
将数据转换为您需要的格式。

// create data view
var view = new google.visualization.DataView(data);

该类DataView允许您提供自己的列，使用方法 -->setColumns(array)

传递您需要的列数组。
我们可以通过列索引来使用现有的列。
比如1对于'diameter'专栏。

// init view columns, use diameter as x-axis
var viewColumns = [1];

或者我们可以传递一个对象{}以用作计算列。
该对象应具有正常的列属性，
例如label& type。

它还可以有一个calc函数，该函数针对数据表中的每一行运行。
它接收数据表和行索引作为参数。
该函数应返回该列的值。

// build view column for each test series
data.getDistinctValues(0).forEach(function (test, index) {
  // add y-axis column for test
  viewColumns.push({
    calc: function (dt, row) {
      // return value for test (string)
      if (dt.getValue(row, 0) === test) {
        return dt.getValue(row, 2);
      }

      // return null if row does not equal test (string)
      return null;
    },
    label: test,
    type: 'number'
  });
});

为了为每个唯一测试（字符串）构建一列，请
使用数据表方法 -->getDistinctValues(columnIndex)
这将返回给定列的唯一值数组。

请参阅以下工作片段...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var data = new google.visualization.DataTable({
    cols: [
      {label: 'test', type: 'string'},
      {label: 'diameter', type: 'number'},
      {label: 'pass', type: 'number'}
    ],
    rows: [
      {c:[{v: 'AAAAAAAAA'}, {v: 20}, {v: 12}]},
      {c:[{v: 'AAAAAAAAA'}, {v: 10}, {v: 19}]},
      {c:[{v: 'AAAAAAAAA'}, {v: 7}, {v: 72}]},
      {c:[{v: 'BBBBBBBBB'}, {v: 21}, {v: 15}]},
      {c:[{v: 'BBBBBBBBB'}, {v: 12}, {v: 52}]},
      {c:[{v: 'BBBBBBBBB'}, {v: 3}, {v: 65}]},
      {c:[{v: 'CCCCCCCCC'}, {v: 19}, {v: 44}]},
      {c:[{v: 'CCCCCCCCC'}, {v: 11}, {v: 22}]},
      {c:[{v: 'CCCCCCCCC'}, {v: 5}, {v: 36}]},
    ]
  });

  var options = {
    title: 'CG',
    legend: 'bottom',
    width: 1000,
    height: 1000
  };

  // create data view
  var view = new google.visualization.DataView(data);

  // init view columns, use diameter as x-axis
  var viewColumns = [1];

  // build view column for each test series
  data.getDistinctValues(0).forEach(function (test, index) {
    // add y-axis column for test
    viewColumns.push({
      calc: function (dt, row) {
        // return value for test (string)
        if (dt.getValue(row, 0) === test) {
          return dt.getValue(row, 2);
        }
        
        // return null if row does not equal test (string)
        return null;
      },
      label: test,
      type: 'number'
    });
  });

  // set view columns
  view.setColumns(viewColumns);

  var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
  chart.draw(view, options);  // use view to draw chart
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="line_chart"></div>

编辑

json 中的数字用引号 ( "v":"100") 括起来，
这很好，但是，数据视图认为您正在尝试返回字符串而不是数字。

一个简单的解决方法是将字符串解析为计算列中的数字...

viewColumns.push({
  calc: function (dt, row) {
    // return value for test (string)
    if (dt.getValue(row, 0) === test) {
      return parseFloat(dt.getValue(row, 2));  // <-- use parseFloat here
    }

    // return null if row does not equal test (string)
    return null;
  },
  label: test,
  type: 'number'
});

请参阅以下更新的片段...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var data = new google.visualization.DataTable({"cols":[{"label":"test","type":"string"},{"label":"diameter","type":"number"},{"label":"pass","type":"number"}],"rows":[{"c":[{"v":"AAAA_s4i_C6_P1"},{"v":"19"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"9.5"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"4.75"},{"v":"99.6086"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"2"},{"v":"98.6021"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.85"},{"v":"95.2807"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.425"},{"v":"86.994"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.25"},{"v":"73.5178"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.125"},{"v":"46.265"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.075"},{"v":"35.9522"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0645"},{"v":"33.6174"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0465"},{"v":"28.815"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0336"},{"v":"24.0125"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0241"},{"v":"20.4106"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0154"},{"v":"18.0093"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0109"},{"v":"16.8087"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0078"},{"v":"14.4075"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0064"},{"v":"12.0062"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0046"},{"v":"9.605"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0033"},{"v":"8.4044"}]},{"c":[{"v":"AAAA_S4i_C6_P1"},{"v":"0.0014"},{"v":"2.4012"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"19"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"9.5"},{"v":"98.4907"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"4.75"},{"v":"97.0266"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"2"},{"v":"95.3437"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.85"},{"v":"91.5629"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.425"},{"v":"86.7029"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.25"},{"v":"64.6961"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.125"},{"v":"28.5647"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.075"},{"v":"18.9485"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0691"},{"v":"16.7525"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0495"},{"v":"13.1627"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0352"},{"v":"11.9661"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0251"},{"v":"9.5728"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0159"},{"v":"8.3762"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0113"},{"v":"7.1796"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.008"},{"v":"5.983"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0066"},{"v":"4.7864"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0047"},{"v":"3.5898"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0033"},{"v":"2.3932"}]},{"c":[{"v":"AAAA_S4i_C6_P2"},{"v":"0.0014"},{"v":"1.1966"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"19"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"9.5"},{"v":"98.5831"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"4.75"},{"v":"98.1927"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"2"},{"v":"96.8337"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.85"},{"v":"92.1854"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.425"},{"v":"81.1248"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.25"},{"v":"59.9439"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.125"},{"v":"25.6355"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.075"},{"v":"17.7442"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0691"},{"v":"15.6747"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0495"},{"v":"12.3158"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0352"},{"v":"11.1962"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.025"},{"v":"10.0766"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0159"},{"v":"8.957"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0113"},{"v":"7.8374"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.008"},{"v":"5.5981"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0066"},{"v":"4.4785"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0047"},{"v":"3.3589"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0033"},{"v":"2.2392"}]},{"c":[{"v":"AAAA_S4i_C6_P3"},{"v":"0.0014"},{"v":"1.1196"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"19"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"9.5"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"4.75"},{"v":"99.2199"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"2"},{"v":"97.2222"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.85"},{"v":"92.0749"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.425"},{"v":"78.3537"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.25"},{"v":"57.07"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.125"},{"v":"25.5148"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.075"},{"v":"18.4487"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0684"},{"v":"17.302"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0491"},{"v":"14.0579"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.035"},{"v":"11.8952"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0249"},{"v":"10.8138"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0159"},{"v":"8.651"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0113"},{"v":"7.5696"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.008"},{"v":"5.4069"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0066"},{"v":"4.3255"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0047"},{"v":"3.2441"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0033"},{"v":"2.1628"}]},{"c":[{"v":"AAAA_S4i_C6_P4"},{"v":"0.0014"},{"v":"1.0814"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"19"},{"v":"100"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"9.5"},{"v":"99.8031"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"4.75"},{"v":"98.9393"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"2"},{"v":"97.0088"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.85"},{"v":"91.9256"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.425"},{"v":"84.381"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.25"},{"v":"61.3373"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.125"},{"v":"29.0731"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.075"},{"v":"20.2975"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0684"},{"v":"18.633"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0495"},{"v":"12.8102"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0352"},{"v":"11.6456"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0251"},{"v":"9.3165"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0159"},{"v":"8.1519"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0113"},{"v":"6.9874"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.008"},{"v":"5.8228"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0066"},{"v":"4.6582"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0047"},{"v":"3.4937"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0033"},{"v":"2.3291"}]},{"c":[{"v":"AAAA_S4i_C6_Pris"},{"v":"0.0014"},{"v":"1.1646"}]}]});

  var options = {
    title: 'CG',
    legend: 'bottom',
    width: 1000,
    height: 1000
  };

  // create data view
  var view = new google.visualization.DataView(data);

  // init view columns, use diameter as x-axis
  var viewColumns = [1];

  // build view column for each test series
  data.getDistinctValues(0).forEach(function (test, index) {
    // add y-axis column for test
    viewColumns.push({
      calc: function (dt, row) {
        // return value for test (string)
        if (dt.getValue(row, 0) === test) {
          return parseFloat(dt.getValue(row, 2));
        }
        
        // return null if row does not equal test (string)
        return null;
      },
      label: test,
      type: 'number'
    });
  });

  // set view columns
  view.setColumns(viewColumns);

  var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
  chart.draw(view, options);  // use view to draw chart
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="line_chart"></div>

php - 如何在php中划分系列以创建用于谷歌图表的json表

问题描述

解决方案

推荐阅读