Thanks very much I'll try that. One last chart I'm having trouble with is
a line chart with dates on the horizontal axis. I tried this with your
example in the chart gallery which works great, but trying to read data
from mySQL through the JSON approach, I get no chart. Here's the JSON
string for an example with 2 data points, and code. Thanks for your help!
{"cols":[{"label":"dates","type":"date"},{"label":"nav","type":"number"}],"rows":[{"c":[{"v":"Date(2013,
1, 01)"},{"v":10}]},{"c":[{"v":"Date(2013, 9, 01)"},{"v":30}]}]}
<?php
$tableName = "test"; // name of data table in target database
$cxn = mysqli_connect(host,user,pass) or die ("Growth database connection
is unavailable."); // open connection to mysql
mysqli_select_db($cxn,$dbname) or die ("Growth data is
unavailable."); // connect to named database
$query = "SELECT dates, nav FROM $tableName"; // data request
$result = mysqli_query($cxn,$query) or die ("Could not execute the growth
query");
$table = array(); // Define array for chart data input
$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' => 'dates', 'type' => 'date'), // Match column
names and types on mySQL database for allocation tables
array('label' => 'nav', 'type' => 'number'),
);
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
$temp = array(); // each column needs to have data
inserted via the $temp array
// Reformat dates
$dateArray = explode('-', $r['dates']);
$year = $dateArray[0];
$month = $dateArray[1] - 1; // subtract 1 from the month
to convert to javascript date format
$day = $dateArray[2];
$temp[] = array('v' => "Date($year, $month, $day)");
$temp[] = array('v' => (float) $r['nav']);
$rows[] = array('c' => $temp); // insert the temp array
into $rows
}
$table['rows'] = $rows; // populate the table with rows of
data
$jsonTable = json_encode($table); // encode the table as JSON
// set up header; first two prevent IE from caching queries
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo $jsonTable; // return the JSON data
?>
and the script in the html that produces no chart:
<script type="text/javascript">
google.load('visualization', '1.0', {'packages':['corechart']});
// Load the Visualization API and the piechart package.
google.setOnLoadCallback(drawChart); // Set a callback to
run when the Google Visualization API is loaded.
// Callback that creates and populates a data table, instantiates the
chart, passes in the data and draws it.
function drawChart() {
var jsonData = $.ajax({
url:
"chart_growth.php<http://www.beladv.com/charts/chart_growth.php>",
dataType:"json",
async: false
}).responseText;
var data = new google.visualization.arrayToDataTable(jsonData);
// Create our data table out of JSON data loaded from server.
// var data = google.visualization.arrayToDataTable([
// ['Year', 'Sales', 'Expenses'],
// ['2004', 1000, 400],
// ['2005', 1170, 460],
// ['2006', 660, 1120],
// ['2007', 1030, 540] ]);
var options = {
'backgroundColor':'#f2f9f6',
'width':300,
'height':300,
'legend': {position: 'none'},
'fontName':'Trebuchet MS'
};
var chart = new
google.visualization.LineChart(document.getElementById('core_chart_growth'));
// Instantiate chart
chart.draw(data, options); // draw chart, passing in
some options.
}
</script>
On Wednesday, October 9, 2013 9:56:22 AM UTC-7, asgallant wrote:
> Showing the values on the bars isn't supported in the API.
>
> I wrote a hack that can make something like it happen that works well if
> you have only 1 series of data in your ColumnChart:
> http://jsfiddle.net/asgallant/QjQNX/
>
> On Wednesday, October 9, 2013 12:34:02 PM UTC-4, Patrick Beaudan wrote:
>>
>> Understood, I thought it'd be more complicated somehow. Got it to work
>> fine thank you very much. Is there a way to automatically show values on
>> the bars of bar charts (or on top of the bars), just like in the slices of
>> pie charts? Seems like a basic function but can't find that option.
>>
>> On Wednesday, October 9, 2013 5:10:35 AM UTC-7, asgallant wrote:
>>
>>> The JSON format is for the DataTable, not the chart - the chart doesn't
>>> care how you got the data into the DataTable. In other words, you don't
>>> have to do anything different when constructing the JSON string for a
>>> DataTable for a ColumnChart (vertical bar chart) than for a PieChart.
>>>
>>> On Wednesday, October 9, 2013 12:49:37 AM UTC-4, Patrick Beaudan wrote:
>>>>
>>>> Is there a sample code similar to that for pie charts in this thread,
>>>> that would show how to build JSON data for a vertical bar chart?
>>>> I'm currently using a version with hard coded data that works fine, but
>>>> would like to read the data from a mysql database using a JSON formatted
>>>> data table. Any example of how to generate that JSON table for a bar chart
>>>> would be much appreciated.
>>>>
>>>> <script type="text/javascript">
>>>> google.load("visualization", "1", {packages:["corechart"]});
>>>> google.setOnLoadCallback(drawChart);
>>>> function drawChart() {
>>>> var data = google.visualization.arrayToDataTable([
>>>> ['Strategy', 'Cumulative Percentage Returns'],
>>>> ['Core', 229 ],
>>>> ['S&P 500', 77 ]
>>>> ]);
>>>>
>>>> var options = {
>>>> 'backgroundColor':'#f2f9f6',
>>>> 'width':300,
>>>> 'height':300,
>>>> 'legend': {position: 'none'},
>>>> 'colors': ['blue'],
>>>> 'vAxis': {baselineColor:'blue', baseline:0, gridlines:
>>>> {count:-1}, textStyle: {color:'red', bold:'true'} },
>>>> 'hAxis': {textStyle: {fontName:'Trebuchet MS', fontSize:10,
>>>> color:'black', bold:'true'} },
>>>> 'fontName':'Trebuchet MS'
>>>> };
>>>>
>>>> var chart = new
>>>> google.visualization.ColumnChart(document.getElementById('core_chart_cumulReturns'));
>>>>
>>>>
>>>> chart.draw(data, options);
>>>> }
>>>> </script>
>>>>
>>>>
>>>> On Tuesday, July 26, 2011 12:24:36 PM UTC-7, GerBen wrote:
>>>>
>>>>> Hello Colleagues,
>>>>> How can I read data from an external database (say, MS SQL Server or
>>>>> MySQL) and show it in a Google Chart?
>>>>> Thank you.
>>>>
>>>>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.
