Change Google Chart bar colors when data table input is from JSON data from server

Question

I have been struggling with google chart API. And I found this brilliant example on SO PHP MySQL Google Chart JSON - Complete Example .

However I was wondering how could I change the bar color from the dafault blue color. I am confused on how should I use the { role: 'style' } function.

Here is my code :

     <?php
    $con=mysql_connect("localhost","username","pass") or die("Failed to connect with database");
    mysql_select_db("rosac", $con); 
    $query = mysql_query("
    SELECT TarikhLulusTahun AS Tahun, COUNT(*) AS Jumlah 
FROM association 
GROUP BY TarikhLulusTahun");


    $rows = array();
    $table = array();
    $table['cols'] = array(

        array('label' => 'Tahun', 'type' => 'string'),
        array('label' => 'Jumlah Persatuan', 'type' => 'number')
        ({type: 'string', role: 'style'})

    );

    $rows = array();
    while($r = mysql_fetch_assoc($query)) {
        $temp = array();
        $temp[] = array('v' => (string) $r['Tahun']); 

        $temp[] = array('v' => (int) $r['Jumlah']); 
        $rows[] = array('c' => $temp);
    }

    $table['rows'] = $rows;
    $jsonTable = json_encode($table);
    //echo $jsonTable;
    ?>

    <html>
      <head>
        <!--Load the Ajax API-->
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.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() {

          // Create our data table out of JSON data loaded from server.
          var data = new google.visualization.DataTable(<?=$jsonTable?>);
          var options = {
               title: 'Jumlah Persatuan Berdaftar Mengikut Tahun',
              is3D: 'true',
              width: 1000,
              height: 1000,
             hAxis: {title: 'Tahun', titleTextStyle: {color: 'red'}},
             vAxis: {title: 'Jumlah Persatuan', titleTextStyle: {color: 'blue'}}
            };

          var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
          chart.draw(data, options);
        }
        </script>
      </head>

      <body>
        <!--this is the div that will hold the pie chart-->
        <div id="chart_div"></div>
      </body>
    </html>

score 7 · Accepted Answer · answered Jan 14 '14 at 17:49

7

You need to do a couple of things. First, your column creation is wrong; this:

$table['cols'] = array(
    array('label' => 'Tahun', 'type' => 'string'),
    array('label' => 'Jumlah Persatuan', 'type' => 'number')
    ({type: 'string', role: 'style'})
);

should be like this:

$table['cols'] = array(
    array('label' => 'Tahun', 'type' => 'string'),
    array('label' => 'Jumlah Persatuan', 'type' => 'number'),
    array('type' => 'string', 'p' => array('role' => 'style'))
);

Then, when you are creating the rows of data, you need to add a cell for the style:

while($r = mysql_fetch_assoc($query)) {
    $temp = array();
    $temp[] = array('v' => (string) $r['Tahun']); 
    $temp[] = array('v' => (int) $r['Jumlah']); 
    $temp[] = array('v' => <insert style here>); 
    $rows[] = array('c' => $temp);
}

answered Jan 14 '14 at 17:49

asgallant

26,060
6
72
87

Can I just add the style right away or i need to pass the value 'v' to $r just like the Tahun and Jumlah rows? – mfmz Jan 15 '14 at 04:10
If you want to color bars individually, you have to use the `'v' => value` syntax. If you are just looking to change the color of the whole data series, that is done using the `colors` option in the chart - you don't need to use the `style` role to accomplish that. – asgallant Jan 15 '14 at 16:50
Oh, and no, you don't need to have the style in `$r` - it can be entered directly or from another PHP variable, it doesn't matter. – asgallant Jan 15 '14 at 16:52

score 2 · Answer 2 · answered Nov 18 '14 at 20:39

    $table = array();
    $table['cols'] = array(
        array('id' => "", 'label' => 'Category', 'pattern' => "", 'type' => 'string'),
        array('id' => "", 'label' => 'Budgeted', 'pattern' => "", 'type' => 'number'),
        array('type' => 'string', 'p' => array('role' => 'style'))
    );

    $rows = array();
    while($r = mysql_fetch_assoc($result_chart)) {

        $temp = array();
        $temp[] = array('v' => (string) $r['Groups']); 
        $temp[] = array('v' => (int) $r['Amount']); 
        $temp[] = array('v' => 'color: #0000cf; stroke-color: #cf001d');
            $rows[] = array('c' => $temp);
    }

    $table['rows'] = $rows;
    $jsonTable = json_encode($table);

Close PHP section, then

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

    google.load('visualization', '1', {'packages':['corechart']});

    google.setOnLoadCallback(drawChart);

    function drawChart() {
        var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

        var options = {
        width: 980,
        height: 500,
        backgroundColor: '#F6F6F6'
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));

        chart.draw(data, options);
    }       
    </script>

tony gil · Answer 3 · 2020-10-13T11:18:02.900

PRIVACY BY DESIGN

Both other answers are useful (UPVOTED both). Nevertheless, I'll toss my hat in the ring for illustrative purposes. Below I simulate a somewhat safer application, using client side AJAX to consume data from restful JSON on a server. This has the advantage of not exposing database structure or credentials (supposing that the RESTful server is separate from the one the final page runs on).

I show 2 alternatives (slightly different declarations), which both work for simple bar color (color and color2). In your final code, use whichever one suits you best.

Serverside RESTful php

$graphData = array(
    'colors' => array(
    'cols' => array(
        array('type' => 'string', 'label' => 'Tahun'),
        array('type' => 'number', 'label' => 'Jumlah'),
        array('type' => 'string', 'p' => array('role' => 'style'))
    ),  
    'rows' => array()
    ),
    'colors2' => array(
    'cols' => array(
        array('type' => 'string', 'label' => 'Tahun'),
        array('type' => 'number', 'label' => 'Jumlah'),
        array('type' => 'string', 'role' => 'style')
    ),
    'rows' => array()
    )
);
[...]
$sql="SELECT  `Tahun`, `Jumlah`, `Color` FROM [...] ";
$result = mysqli_query($conn, $sql) or trigger_error(mysqli_error($conn));
while($row = mysqli_fetch_array($result)){
    $graphData['colors']['rows'][] = array('c' => array(
        array('v' => $row['Tahun']),
        array('v' => (int)$row['Jumlah']),
        array('v' => $row['Color'])
    ));
    $graphData['colors2']['rows'][] = array('c' => array(
        array('v' => $row['Tahun']),
        array('v' => (int)$row['Jumlah']),
        array('v' => $row['Color'])
    ));
}
// $row['Color'] is formatted as "color: #FF0000"
echo json_encode($graphData);

Client side browser JavaScript

var jsonDataAjax = $.ajax({
    url: "http://yourdomain.com/yourRestfulJson.php",
    dataType: "json",
    async: false
    }).responseText;
var jsonData = eval("(" + jsonDataAjax + ")");

var jsonColors = new google.visualization.DataTable(jsonData.colors);
var jsonColors2 = new google.visualization.DataTable(jsonData.colors2);

var viewColors = new google.visualization.DataView(jsonColors);
viewColors.setColumns([0, 1,
           { calc: "stringify",
             sourceColumn: 1,
             type: "string",
             role: "annotation" },
           2]);   
                    
var viewColors2 = new google.visualization.DataView(jsonColors2);
viewColors2.setColumns([0, 1,
           { calc: "stringify",
             sourceColumn: 1,
             type: "string",
             role: "annotation" },
           2, {type: 'string', role: 'style'}]);        

var options = {
    title: "ColumnChart Color Testing Graph",
    width: 1200,
    height: 400,
    bar: {groupWidth: "95%"},
    legend: { position: "none" },
};

var chart1 = new google.visualization.ColumnChart(document.getElementById('bar1_div'));
var chart2 = new google.visualization.ColumnChart(document.getElementById('bar2_div'));

chart1.draw(viewColors, options);
chart2.draw(viewColors2, options);

Change Google Chart bar colors when data table input is from JSON data from server

3 Answers3

Linked