Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3378
  • Last Modified:

JSON and SQL Query Format for Highcharts

HI EE,

i'm trying to build a json array from the data below to fit an json array for Highchart

I've been grabbing the sql using php like so


<?php
/* Set Connection Credentials */
$server="---------";
$database="-----------";
$user="----------";

$password="----------------";
 
/* Connect using SQL Server Authentication. */
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);
if (!$connection) {
  die("Failed");
}

 
/* TSQL Query */
$query = "Select * from data";

$result = odbc_exec($connection,$query);
if (!$result) {
  die("Invalid query");
}


/* Process results */
$json = array();
 
     while ($row = odbc_fetch_array($result)) {
    $json = 
array(
$row['JAN'],
$row['FEB'],
$row['MAR'],
$row['APR'],
$row['MAY'],
$row['JUN'],
$row['JUL'],
$row['AUG'],
$row['SEP'],
$row['OCT'],
$row['NOV'],
$row['DEC']
);
}

echo json_encode($json,JSON_NUMERIC_CHECK );
$jsonData = json_encode($json,JSON_NUMERIC_CHECK );


 

Open in new window


then pass it to javascript via
var jsonData = <?php echo $jsonData ?>;

Open in new window


http://www.highcharts.com/demo/column-basic/gray/
http://jsfiddle.net/gh/get/jquery/1.9.1/highslide-software/highcharts.com/tree/master/samples/highcharts/demo/column-basic/

if you have a look at the jsfiddle example the array they accept is like:

series: [ {name: , data: },{name: , data: },{name: , data: } ]

http://sqlfiddle.com/#!3/d7917/1

I've managed to get the query coming into an array.... but just not in the right format accepted.

CREATE TABLE Data
(
[Name]                  varchar(50),
[year]				    int ,
[JAN]					decimal(18,2),
[FEB]					decimal(18,2),
[MAR]					decimal(18,2),
[APR]					decimal(18,2),
[MAY]					decimal(18,2),
[JUN]					decimal(18,2),
[JUL]					decimal(18,2),
[AUG]					decimal(18,2),
[SEP]					decimal(18,2),
[OCT]					decimal(18,2),
[NOV]					decimal(18,2),
[DEC]				    decimal(18,2),

)


INSERT INTO Data(Name ,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) VALUES('Ross ','2014','2.49186113698212','72.5678518331612','66.8550844045816','42.9450987262078','96.8674771151895','65.9733850876795','70.6832759133776','64.5189574007928','8.02819739752803','43.7684500291072','97.2627912725123','59.1845987363436');
INSERT INTO Data(Name ,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) VALUES('Experts ','2014','5.70662051512079','82.3937933850884','43.281056006308','2.49662182278394','98.5366074743309','69.0053068787614','99.8544513274818','82.6814816203346','68.0443024623437','59.8105857753019','38.2948202287738','23.6317858544775');
INSERT INTO Data(Name ,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) VALUES('Bob ','2014','17.6009587556784','21.059039646663','47.0733176241224','59.1747180888193','16.9108323631046','73.1389080240345','15.3447797473347','96.6017139690297','34.631708420404','1.23205112737876','69.7027277128418','20.7339649755986');
INSERT INTO Data(Name ,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) VALUES('Karen ','2014','46.7851093577968','65.5093766180721','99.9915369491519','47.8845574260545','40.042176946126','23.5326631757407','66.7100204785974','37.1951648458613','92.961084730526','4.09362163291895','20.9460381219186','43.634320743665');
INSERT INTO Data(Name ,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) VALUES('Elvis','2014','20.8742375818463','48.4250301161502','36.1121696406429','57.6749683144687','0.541033206348285','71.5154202493314','29.997449385212','68.703749322646','91.0389349436526','63.0273450742312','82.6866571178043','68.5641375587952');

Open in new window


i feel i'm going round in circles

Thank you for your help
0
Ross Turner
Asked:
Ross Turner
1 Solution
 
hieloCommented:
try:
<?php
/* Set Connection Credentials */
$server="---------";
$database="-----------";
$user="----------";

$password="----------------";
 
/* Connect using SQL Server Authentication. */
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);
if (!$connection) {
  die("Failed");
}

 
/* TSQL Query */
$query = "Select * from data";

$result = odbc_exec($connection,$query);
if (!$result) {
  die("Invalid query");
}


/* Process results */
$json = array();
 
while ($row = odbc_fetch_array($result)) {
	$json[] = array('name'=>$row['Name'],'data'=>array( $row['JAN']
														,$row['FEB']
														,$row['MAR']
														,$row['APR']
														,$row['MAY']
														,$row['JUN']
														,$row['JUL']
														,$row['AUG']
														,$row['SEP']
														,$row['OCT']
														,$row['NOV']
														,$row['DEC']
														)
					);
}

echo json_encode($json,JSON_NUMERIC_CHECK );
$jsonData = json_encode($json,JSON_NUMERIC_CHECK );


 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now