Improve company productivity with a Business Account.Sign Up

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

JSChart XML/JSON: File not loaded or malformed color/data/option set

Trying to get some data loaded dynamically into the JSChart plugin. Repeatedly getting the error listed above, not sure what to do, and their support is terrible.

HTML Page
<script type="text/javascript" src="../../../jschart/sources/jscharts.js"></script>

<div id="chartcontainer">Loading Graph...</div>

<script type="text/javascript">
var myChart = new JSChart('chartcontainer', 'line');
myChart.setDataJSON("data.php");
myChart.draw();
</script>

Open in new window


Data.php

//Figure out last month's period
$currentPeriod = date("Ym");

//Use last month for Result purposes
$lastPeriod = ($currentPeriod - 1);

//Figure out 60 months ago from last month
$lastSixty = date("Ym", strtotime($lastPeriod . " -60 months"));

$returnArray = array();

//Run query to get data for this graph
$query = "select dw_inv_loc_totals.hire_net,
				 dw_inv_loc_totals.sales_net,
				 dw_inv_loc_totals.month,
				 dw_inv_loc_totals.yearmonth,
				 dw_inv_loc_totals.period
			FROM dw_inv_loc_totals 
		   WHERE dw_inv_loc_totals.period >= '".$lastSixty."' AND  
				 dw_inv_loc_totals.period <= '".$lastPeriod."' AND
				 dw_inv_loc_totals.locationid = '3'
		ORDER BY dw_inv_loc_totals.yearmonth";
		
$result = mysql_query($query) or trigger_error(E_USER_ERROR);

while($row=mysql_fetch_array($result)) 
	{
	$rowArray['period'] = $row['period'];
    $rowArray['hire_net'] = $row['hire_net'];
    // $rowArray['sales_net'] = $row['sales_net'];

	array_push($returnArray, $rowArray);
	}

	echo json_encode($returnArray);

Open in new window

0
t3chguy
Asked:
t3chguy
  • 12
  • 10
2 Solutions
 
Chris StanyonCommented:
I've not used jscharts before but from their docs it looks like the JSON file has to be in a specific format. Yours is creating an array with values for period and hire_net

Have a read through their DOCs for the right forma to use:

http://www.jscharts.com/how-to-use

From their site, here's an example of how the JSON file should look:

{
	"JSChart" : {
		"datasets" : [
			{
				"type" : "pie",
				"data" : [
					{
						"unit" : "Unit_1",
						"value" : "20"
					},
					{
						"unit" : "Unit_2",
						"value" : "10"
					},
					{
						"unit" : "Unit_3",
						"value" : "30"
					},
					{
						"unit" : "Unit_4",
						"value" : "10"
					},
					{
						"unit" : "Unit_5",
						"value" : "5"
					}
				]
			}
		]
	}
}

Open in new window

0
 
t3chguyAuthor Commented:
Does this mean I cannot have a dynamic chart?  Or how could I manipulate my php code to be in the format they need?
0
 
Chris StanyonCommented:
No, you can have a dynamic chart, you just need to make sure your data.php file outputs the correct format. Once you've queried the database (you should be using PDO by the way), then build your JSON data something like this:

$data = array();

while ($row = mysql_fetch_array($result)):
	$data[] = array(
		'unit' => $row['hire_net'],
		'value' => $row['period']
	);
endwhile;

$dataSets = array(
	'datasets' => array(
		'type' => 'line',
		'data' => $data
	)
);

$returnArray = array(
	'JSChart' => $dataSets
);

echo json_encode($returnArray);

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
t3chguyAuthor Commented:
I still get the same error message :\
0
 
Chris StanyonCommented:
OK. Try this (sorry, I can't test it as JSCharts expects me to sign up to grab the scripts). Basically the datasets looks like it need to be an array as well.

$data = array();

while ($row = mysql_fetch_array($result)):
	$data[] = array(
		'unit' => $row['hire_net'],
		'value' => $row['period']
	);
endwhile;

$dataSet[] = array(
	'type' => 'pie',
	'data' => $data
);

$dataSets = array(
	'datasets' => $dataSet
);

$returnArray = array(
	'JSChart' => $dataSets
);

echo json_encode($returnArray);
?>

Open in new window

0
 
t3chguyAuthor Commented:
Same Error.  I have attached a copy of the jscharts.js file I downloaded.
jscharts.js
0
 
Chris StanyonCommented:
Thanks. I'll have a look shortly and get back to you.
0
 
Chris StanyonCommented:
Right. Looked into this a little more and I don't think the problem's with the data. It's down to when you're calling the script. Because the script relies on element in the DOM being available, you need to defer the running of the script until the DOM is ready. As you're using jQuery, the easiest way is to wrap your script in the document ready block:

<script type="text/javascript">
$(document).ready(function() {
	var myChart = new JSChart('chartcontainer', 'line');
	myChart.setDataJSON('data.php');
	myChart.draw();
});
</script>

Open in new window

0
 
t3chguyAuthor Commented:
That makes sense, unfortunately, still getting the same error message: JSChart XML/JSON: File not loaded or malformed color/data/option set
0
 
Chris StanyonCommented:
Right. One step at a time. Just call your data.php file in your browser, and then copy and paste the output of that. Let's make sure your data is OK first.

As I said before you should be using PDO (or mysqli) for your data access, but we can address that later. Also, why is your query pulling out 5 columns when you only need 2?
0
 
t3chguyAuthor Commented:
{"JSChart":{"datasets":[{"type":"line","data":[{"unit":"200810","value":"1570777.93"},{"unit":"200811","value":"1119635.18"},{"unit":"200812","value":"1093127.47"},{"unit":"200901","value":"831062.68"},{"unit":"200902","value":"654139.56"},{"unit":"200903","value":"1067342.97"},{"unit":"200904","value":"1172460.47"},{"unit":"200905","value":"1217131.51"},{"unit":"200906","value":"877265.81"},{"unit":"200907","value":"837059.76"},{"unit":"200908","value":"815402.51"},{"unit":"200909","value":"825353.85"},{"unit":"200910","value":"937616.15"},{"unit":"200911","value":"1191848.30"},{"unit":"200912","value":"743521.36"},{"unit":"201001","value":"641017.13"},{"unit":"201002","value":"839450.08"},{"unit":"201003","value":"1279926.62"},{"unit":"201004","value":"1055023.67"},{"unit":"201005","value":"1003827.27"},{"unit":"201006","value":"799022.89"},{"unit":"201007","value":"735692.57"},{"unit":"201008","value":"998754.15"},{"unit":"201009","value":"989433.34"},{"unit":"201010","value":"1128373.82"},{"unit":"201011","value":"1401278.59"},{"unit":"201012","value":"883235.87"},{"unit":"201101","value":"633714.46"},{"unit":"201102","value":"668736.63"},{"unit":"201103","value":"778523.97"},{"unit":"201104","value":"927132.87"},{"unit":"201105","value":"1210070.78"},{"unit":"201106","value":"892750.61"},{"unit":"201107","value":"895807.67"},{"unit":"201108","value":"807426.98"},{"unit":"201109","value":"821446.06"},{"unit":"201110","value":"961199.73"},{"unit":"201111","value":"1004498.92"},{"unit":"201112","value":"950868.04"},{"unit":"201201","value":"828012.15"},{"unit":"201202","value":"679551.17"},{"unit":"201203","value":"884298.72"},{"unit":"201204","value":"1114554.62"},{"unit":"201205","value":"1323496.23"},{"unit":"201206","value":"1151867.41"},{"unit":"201207","value":"1230307.18"},{"unit":"201208","value":"1152093.16"},{"unit":"201209","value":"1104967.27"},{"unit":"201210","value":"1399892.11"}]}]}}

Open in new window


I copied the query from the old reporting that we were doing.  I can clean that up.  The ultimate goal of this would be to display two sets of numbers in two different lines, to create a stacked graph along with the dates, but I figured I'd try to at least get it to do the first set of numbers first.
0
 
Chris StanyonCommented:
OK. The data you've posted is fine and when I create a JSON file from it and use that I get a chart working.

Next step. Copy and paste that data into a file and call it data.json - we'll use it to work with static data for the time being. Once you've done that, create another file containing the following. It assumes the jscharts.js file is in the same folder as the file (if it's not, make sure the path is set in the following file):

<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8" />
		<title>Chris Stanyon</title>
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
		<script type="text/javascript" src="jscharts.js"></script>
		<script type="text/javascript">
		$(document).ready(function() {
			var myChart = new JSChart('chartcontainer', 'line');
			myChart.setDataJSON('data.json');
			myChart.draw();
		});
		</script>
	</head>

	<body>
		<div id="chartcontainer">Loading Graph...</div>
	</body>
</html>

Open in new window

Once you've got that, call it in your browser - that's exactly what I've used and I get a chart rendered on the page.
0
 
t3chguyAuthor Commented:
Done.  I've attached a picture of the chart rendered by the step above.
Chart-Example.png
0
 
Chris StanyonCommented:
Excellent. All good so far. Now link your dynamic data back into the script and make sure that works. Change the data.json to data.php and call it again - if everything works as it should then you'll see the same chart. Make sure the data.php file is in the same folder (or change the path accordingly)
0
 
t3chguyAuthor Commented:
As soon as i did that, the error message started appearing again.

JSChart XML/JSON: File not loaded or malformed color/data/option set
0
 
Chris StanyonCommented:
Hmmm. Are you absolutely sure the file exists in the right place and you've spelt the file name correctly. Everything we've gone through should result in the chart displaying fine. There's no difference in the data output by the static JSON file and the dynamic PHP file, so you shouldn't get a problem.

For debugging purposes, bring up the console in your browser (preferably Firefox/Firebug or Chrome). Call you page and see if you get any output in the console (you press F12 to get the console)
0
 
t3chguyAuthor Commented:
The output I'm getting is the data file I posted earlier...

There is a syntax error on line 16 of jchart.js

SyntaxError: JSON.parse: unexpected character

      

... ml.bz[2l]===\'1r\')?ml.bA:ml.bz[2l],1M]};1a.tP=1c(id,1M){if(1f id!==\'1r\'&&1f ...

jscharts.js (line 16)
0
 
Chris StanyonCommented:
Right. The only way I can get that error is if the data.php file is echoing out some additional information before the json_encode() function. These things need to be precise so double check your data.php file. FYI, here's the data.php file I'm using (it's using PDO instead of mysql and uses the PHP DateTime class). Try this (substitute your own database details):

<?php
//Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=yourDB", "username", "password");

//prepare the query
$stmt = $pdo->prepare("
	SELECT period, hire_net
	FROM dw_inv_loc_totals
	WHERE (period >= :lastSixty AND period <= :lastPeriod) AND locationid = :locationId
	ORDER BY period
");

//prep the parameters
$now = new DateTime();
$parameters = array(
	'lastPeriod' => $now->modify('first day of last month')->format("Ym"),
	'lastSixty' => $now->modify('first day of -60 months')->format("Ym"),
	'locationId' => 3
);

//run the query
$stmt->execute($parameters);

//get ready for the chart data
$data = array();

//loop through the results to populate the data
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) :
	$data[] = array(
		'unit' => $row->period,
		'value' =>  $row->hire_net
	);
endwhile;

//set the chart type and dataset
$dataSets = array(
	'type' => 'line',
	'data' => $data
);

//create the JSChart info
$JSChart = array(
	'JSChart' => array(
		'datasets' => array($dataSets)
	)
);

//output everything as JSON
echo json_encode($JSChart);

Open in new window

0
 
t3chguyAuthor Commented:
on data.php I'm getting


Fatal error: Call to a member function format() on a non-object in /san-dev/htdocs/reports/location/locationrevenues/data.php on line 16


I'm using php 5.2, yes old, old, old, but it's my work and we're stuck with it for awhile :\

Is that what is holding me back here?
0
 
Chris StanyonCommented:
Hmmm. I thought the DateTime class was PHP>=5.2. You may have to revert back to your own script, but do double check it.

Alternatively, switch out lines 14-19 of my script with the following (avoids using the DateTime class):

$currentPeriod = date("Ym");
$lastPeriod = ($currentPeriod - 1);
$lastSixty = date("Ym", strtotime($lastPeriod . " -60 months"));
$parameters = array(
	'lastPeriod' => $lastPeriod,
	'lastSixty' => $lastSixty,
	'locationId' => 3
);

Open in new window

I'm calling it a day now so will revisit tomorrow. In the mean time, if you can provide a live link to your scripts, I can take a 'real-time' look at it.
0
 
t3chguyAuthor Commented:
That worked!  No idea how we got here, but this solution seems far too complicated for all the reports they want charts for.  Appreciate ALL of your hard work and patience as we worked through this!
0
 
Chris StanyonCommented:
Excellent. glad we got there in the end :)
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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