JSON from mySQL and Coloring in Google Charts

Question

I would like to get some help with coloring data differently in with google chart. I'm trying to compare two domains with date and rank value.

If I leave out the 'domain' data and just have date and value data, it will display a chart with the date data on the y-axis and the value data on the x-axis. I would like to use the domain data in my table to differentiate the data I'm comparing, one domain in one color vs. another domain in another color in a bar chart with the legend identifying the domain.

Basically using the code below I get the following error: All series on a given axis must be of the same data type

If there's different and or easier way of going about this let me know.

Table Structure:

Column Type Null Default Comments

date text No
value text No
domain text No

Here's what I use to change my mysql data into Google friendly JSON:

<?php
/* $server = the IP address or network name of the server
 * $userName = the user to log into the database with
 * $password = the database account password
 * $databaseName = the name of the database to pull data from
 */
$host="127.0.0.1"; //replace with your hostname
$username="XXXXXXX"; //replace with your username
$password="XXX"; //replace with your password
$db_name="KYW_data"; //replace with your database
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name") or die ("cannot select DB");

mysql_select_db($db_name, $con); 

// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)
$query = mysql_query('SELECT date, value FROM KYW_Compete_rank');

$table = array();
$table['cols'] = array(
/* define your DataTable columns here
* each column gets its own array
* syntax of the arrays is:
* label => column label
* type => data type of column (string, number, date, datetime, boolean)
*/
    array('label' => 'date', 'type' => 'string'),
array('label' => 'value', 'type' => 'number'),
array('label' => 'value', 'type' => 'string')
// etc...
);

$rows = array();
while($r = mysql_fetch_assoc($query)) {
    $temp = array();
// each column needs to have data inserted via the $temp array
$temp[] = array('v' => $r['date']);
$temp[] = array('v' => $r['value']);
$temp[] = array('v' => $r['domain']);
// etc...
// insert the temp array into $rows
    $rows[] = array('c' => $temp);
}

Code for Displaying Google Chart:

<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);

    function drawChart() {
      var jsonData = $.ajax({
          url: "fetch.php",
          dataType:"json",
          async: false
          }).responseText;

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);

      // Instantiate and draw our chart, passing in some options.
      new google.visualization.BarChart(document.getElementById('visualization')).
            draw(data,
                 {title:"Rank Standings",
                  width:600, height:400,
                  vAxis: {title: "Year and Month"},
                  hAxis: {title: "Rank"}}
            );
    }

    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->
    <div id="visualization"></div>
  </body>
</html>

asgallant asgallant · Accepted Answer · 2014-04-17T21:20:29

To get the legend to display two different series, you have to split your data into two columns. You can use SQL like this to split your data:

SELECT
    date,
    SUM(IF(<condition for series 1>, value, 0)) AS value_1,
    SUM(IF(<condition for series 2>, value, 0)) AS value_2
FROM KYW_Compete_rank
GROUP BY date

and then populate your DataTable:

$table = array(
    'cols' => array(
        array('label' => 'date', 'type' => 'string'),
        array('label' => 'value 1', 'type' => 'number'),
        array('label' => 'value 2', 'type' => 'number')
    )
    'rows' => array()
);
while($r = mysql_fetch_assoc($query)) {
    $data['rows'][] = array('c' => array(
        array('v' => $r['date']),
        array('v' => $r['value_1']),
        array('v' => $r['value_2'])
    ));
}
echo json_encode($data, JSON_NUMERIC_CHECK);

JSON from mySQL and Coloring in Google Charts

1 Answers