Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How to divide series in php to create a json table for google charts

Tags:

json

php

mysqli

charts

google-visualization

I have to plot many curves using data from a database. The database structure is like this:

enter image description here

And the result i want is like this (in this case with only 2 series and with a logarithmic axis)

enter image description here

With test (string) as name of the series (in legend) and diameter and pass as X and Y values. i tried many solutions but nothing; I think i understand that i nedd to split result for test (string) but i don't know how. Can you help me?

<?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>
like image 305
mik1mik3 Avatar asked Oct 16 '22 07:10

mik1mik3

1 Answers

the data format for google's line chart,
calls for the x-axis to be the first column in the data table.
each additional column is considered a series.

in order to have a series for each test (string),
the data table would need to be in the following format. 

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

this may be difficult to build in your query / php,
especially if there are many more series for test (string).

as such, you can leave the php alone,
and use google's DataView class,
to transform the data into the format you need. 

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

the DataView class allows you to provide your own columns, using method --> setColumns(array)

pass an array of the columns you need.
we can pass the column index to use an existing column.
such as 1 for the 'diameter' column. 

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

or we can pass an object {} to use as a calculated column.
the object should have the normal column properties,
such label & type.

it can also have a calc function, which is run for each row in the data table.
it receives the data table and row index as arguments.
the function should return the value for that column. 

// 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'
  });
});

in order to build a column for each unique test (string),
use data table method --> getDistinctValues(columnIndex)
this will return an array of the unique values for the given column.

see following working snippet... 

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>

EDIT

the numbers in your json are wrapped in quotes ("v":"100"),
which is fine, however, the data view thinks you're trying to return a string instead of a number.

an easy fix is to parse the string into a number in the calculated column... 

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'
});

see following updated snippet... 

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>
like image 120
WhiteHat Avatar answered Oct 20 '22 22:10

WhiteHat
Sign in to Comment

Related questions

Neo4j PHP Graphaware '400 Bad Content-Type header' error
Find text inside curly braces and replace text including curly braces
Axios request giving getter setter methods instead of data queried
Equivalent of Function.prototype.apply in PHP?
php regex: Alternative to backreference in negative lookbehind
how to combine php array of object by unique id?
Logger in Clean Architecture
VS Code Folding Based on Brackets - Not Indentation
Filter products from a specific custom meta data in Woocommerce shop page
fetch data from MSSQL server by ajax through Php
Laravel command called from Laravel job
Sending a mail using PHPMailer with Gmail Account
Slicing a time range into parts
What is the difference between EOT and HTML? in PHP
Yii2: How to add two fields in orderby() of Find()
How to tell PHP to use SameSite=None for cross-site cookies?
How to get parent product id in magento?
IN Flutter Web getting 'XMLHttpRequest' error while making HTTP call
Facebook crawler is hitting my server hard and ignoring directives. Accessing same resources multiple times
Binding multiple values in pdo

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!