Im using this to draw column highcharts jsfiddle i use this to gt JSON:
<?php
$query = mysql_query("SELECT
sales_raport_all.from_date,
sales_raport_all.to_date,
sales_raport_all.konto,
SUM(sales_raport_all.saldo_sprzedazy),
SUM(sales_raport_all.wartosc_kosztowa),
SUM(sales_raport_all.marza),
klienci_ax_all.sales_group,
klienci_ax_all.nazwa
FROM
sales_raport_all
INNER JOIN
klienci_ax_all
ON
sales_raport_all.konto=klienci_ax_all.konto_odbiorcy
WHERE
YEAR(from_date) = YEAR(CURDATE())
GROUP BY
sales_raport_all.from_date,
klienci_ax_all.sales_group
ORDER BY
sales_raport_all.from_date,
klienci_ax_all.sales_group");
$raw = array();
$dates = array();
while ($r = mysql_fetch_array($query)) {
$date = $r['from_date'];
if (!in_array($date, $dates)) $dates[] = $date;
$sales_group = $r['sales_group'];
$raw[$sales_group][$date] = intval($r['SUM(sales_raport_all.saldo_sprzedazy)']);
}
$data = array();
$data[0] = array('name' => "Date", 'data' => $dates);
foreach ($raw as $name => $d) {
$new_data = array('name' => $name, 'data' => array());
foreach ($dates as $date) {
$new_data['data'][] = isset($d[$date]) ? $d[$date] : 0;
}
$data[] = $new_data;
}
print json_encode($data);
in fiddle i use
chart3Options.series[0] = json[1];
...
is there a simple way to define all data in json? this data is variable and if i declare 11 variables and there will be only 7 then charts will not draw JSON output for one date:
[{"name":"Date","data":["2014-01-01"]},{"name":"IN","data":[2580]},{"name":"KD","data":[5030]},{"name":"\u0141S","data":[12628]},{"name":"NN","data":[400]},{"name":"SG","data":[12979]},{"name":"TD","data":[15096]}]
// EDIT
i create new file:
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highcharts Example</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var options = {
chart: {
renderTo: 'container',
type: 'column',
marginRight: 130,
marginBottom: 25
},
title: {
text: 'test',
x: -20 //center
},
subtitle: {
text: '',
x: -20
},
xAxis: {
categories: []
},
yAxis: {
title: {
text: 'test'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
formatter: function() {
return '<b>'+ this.series.name +'</b><br/>'+
this.x +': '+ this.y;
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 100,
borderWidth: 0
},
series: []
}
$.getJSON("test2.php", function(json) {
options.xAxis.categories = json[0]['category'];
options.series[0] = {};
options.series[0].name = json[0]['name'];
options.series[0].data = json[0]['data'];
chart = new Highcharts.Chart(options);
});
});
</script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
</head>
<body>
<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>
</body>
</html>
and test2.php
<?php
$db_host = '******';
$db_user = '******';
$db_pass = '******';
$db_database = '******';
$link = mysql_connect($db_host,$db_user,$db_pass) or die('Nawiązanie połączenia z bazą danych nie było możliwe');
mysql_select_db($db_database,$link);
mysql_query("SET names UTF8");
$query = mysql_query("SELECT
sales_raport_all.from_date,
sales_raport_all.to_date,
sales_raport_all.konto,
SUM(sales_raport_all.saldo_sprzedazy),
SUM(sales_raport_all.wartosc_kosztowa),
SUM(sales_raport_all.marza),
klienci_ax_all.sales_group,
klienci_ax_all.nazwa
FROM
sales_raport_all
INNER JOIN
klienci_ax_all
ON
sales_raport_all.konto=klienci_ax_all.konto_odbiorcy
WHERE
YEAR(from_date) = YEAR(CURDATE())
GROUP BY
sales_raport_all.from_date,
klienci_ax_all.sales_group
ORDER BY
sales_raport_all.from_date,
klienci_ax_all.sales_group");
$result = array();
while($r = mysql_fetch_array($query)) {
$grupa = $r['sales_group'];
$datetime = $r['from_date'];
$result['name'][] = $datetime;
$result['category'][] = $grupa;
$result['data'][] = intval($r['SUM(sales_raport_all.saldo_sprzedazy)']);
}
$json = array();
array_push($json,$result);
print json_encode($json);
?>
JSON give me:
[{"name":["2014-01-01","2014-01-01","2014-01-01","2014-01-01","2014-01-01","2014-01-01"],"category":["IN","KD","\u0141S","NN","SG","TD"],"data":[2580,5030,12628,400,12979,15096]}]
Series looks greate but i dont know how to change category as in example http://jsfiddle.net/rubJS/
You can handle it in javascript. In other words, instead of defining variable for each <x,y> pair, return the data and let javascript construct the series. For example, given your current output you can prepare X and Y values (in javascript) in separate arrays and write a function pushing these values into series. It can be done like this (using jQuery as an example):
function build_chart(x_values, y_values, options)
{
jQuery.each(x_values, function(item) {
options.xAxis.categories.push(x_values[item]);
});
var series = {
data: []
};
jQuery.each(y_values, function(item) {
series.data.push(parseInt(y_values[item]));
});
options.series.push(series);
chart = new Highcharts.Chart(options);
return chart;
}
Where variable options defines a chart template without series member (which is added by the above function)
EDIT
Following the edit in the question, here is the jsFiddle supporting it. Note that the data in JSON is represented as array of arrays. First element in each array corresponds to the first category, second element in each array corresponds to the second category etc.
Apart from incorrect json format (which was mentioed) you need to push your dates as categorries in highcharts or use datetime type of axis, and then parse your date to timestamp.