Solved

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

Posted on 2013-10-22
22
1,321 Views
Last Modified: 2013-10-23
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
Comment
Question by:t3chguy
  • 12
  • 10
22 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
I still get the same error message :\
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
Same Error.  I have attached a copy of the jscharts.js file I downloaded.
jscharts.js
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Thanks. I'll have a look shortly and get back to you.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
That makes sense, unfortunately, still getting the same error message: JSChart XML/JSON: File not loaded or malformed color/data/option set
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
{"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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
Done.  I've attached a picture of the chart rendered by the step above.
Chart-Example.png
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:t3chguy
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Excellent. glad we got there in the end :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

How to build a simple, quick and effective accordion menu using just 15 lines of jQuery and 2 css classes
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now