How can I use PHP MySQL Dynamic data

[photon77]

All right! I used the line you provided, which worked beautifully. Once I realized it was something I needed to do, I downloaded jquery, and changed the incorrect
<script src=”jquery.js”></script>
to the correct
<script src=”/jquery/jquery-1.10.1.js”></script> <!– make sure jquery is available! –>
Which works like a charm as evidenced in:
http://www.capegreenenergy.com/dev/sunilJSON.html

Thanks again for your ultra fast help. I look forward to playing with this over the summer. Jack Adams

[Savak]

Any chance to get two sets of datapoints… i mean two queries from same data file… retrieving those two json datapoints to chart.render function and build a multiseries chart????

[Savak]

Well… with Sunil help (thanks, once more) i make it to work with this:

HTML

I have several Graphs in the same page, using tabs loading them with ajax, so i choose to load then with:

<?php include ('includes/graph_ValongoCM.php'); ?>

SCRIPT – LOAD DATA

<script type="text/javascript">
function vlg_cm() {
	$(document).ready(function () {
	$.getJSON("includes/graph_ValongoCM-data.php", function (result){
	var chart = new CanvasJS.Chart("chartContainer1", {
	axisX: {	
	title: "Partidos ",
	},
	axisY: {	
	title: "N.º Votos",
	},
	data: 	[
				{
					type:"column",
					toolTipContent: "Votos: {y} - {name}",   
					dataPoints: result[0],
				},
				{
					type:"column",
					toolTipContent: "Votos: {y} - {name}",   
					dataPoints: result[1],
				},		
			]
	});

	chart.render();

	setTimeout(vlg_cm, 30000);
	});
	});
	}
	vlg_cm();
</script>

PHP – QUERY DATA AND ENCODE JSON

<?php
header('Content-Type: application/json');

$con = mysqli_connect('localhost','root', '','autarquicas2013');
// Check connection
if (mysqli_connect_errno($con))
{
echo "Failed to connect to DataBase: " . mysqli_connect_error();
}else
{
// TWO ARRAYS OF DATAPOINTS
$data_points09 = array();
$data_points13 = array();
// FIRST QUERY
$result = mysqli_query($con, 
"SELECT partidos.designa AS PARTIDO, sum( camara09.votos ) AS VOTOS, (SELECT SUM(votos) FROM camara09 WHERE id_freguesia = 5) AS TOTAL
FROM camara09
INNER JOIN partidos ON partidos.id = camara09.id_partido
WHERE id_freguesia = 5
GROUP BY partidos.designa
ORDER BY partidos.id ASC ");

while($row = mysqli_fetch_array($result))
{
$point = array("label" => $row['PARTIDO'] , "name" => (round((($row['VOTOS'] / $row['TOTAL']) * 100), 2)  . '%'),  "y" => $row['VOTOS']);
array_push($data_points09, $point);
}
// SECOND QUERY
$result13 = mysqli_query($con, 
"SELECT partidos.designa AS PARTIDO, sum( camara13.votos ) AS VOTOS, (SELECT SUM(votos) FROM camara13 WHERE id_freguesia = 5) AS TOTAL
FROM camara13
INNER JOIN partidos ON partidos.id = camara13.id_partido
WHERE id_freguesia = 5
GROUP BY partidos.designa
ORDER BY partidos.id ASC ");

while($row = mysqli_fetch_array($result13))
{
$point = array("label" => $row['PARTIDO'] , "name" => (round((($row['VOTOS'] / $row['TOTAL']) * 100), 2)  . '%'),  "y" => $row['VOTOS']);
array_push($data_points13, $point);
}

//THIRD ARRAY - TO JOIN THE PREVIOUS ONE'S
$data_points = array(); 
//PUSHING ARRAY 1 AND ARRAY 2 INTO ARRAY 3
array_push($data_points, $data_points13);
array_push($data_points, $data_points09);

//JSON ENCODE THE ARRAY 3
echo json_encode($data_points, JSON_NUMERIC_CHECK);
}
mysqli_close($con);
?>

Hope its handy to someone else!!

[Sunil Urs]

Savak, thanks for sharing the code…

[Savak]

@Sunil, i tried to concatenate both json_encode(datapoints) as you said, but with no luck.. So i created a new (the third array in example) and pushed the other ones into it… finally the json_encode!!!

[Sunil Urs]

Yeah, I observed it. This is in fact a cleaner way of doing it. What I suggested was quick and dirty approach which probably was not right.

[sparky]

Hi Sunil, I have tried a lot of things but my graph is still not rendering. I don’t know what could be the problem. jquery not running? or any other issue that I am not aware of.

Here is my code:

My data.php file:

<?php ob_start(); ?>

<?php
include ‘core/init.php’;
protect_page();
include ‘includes/overall/overallheader.php’;
?>

<h1>Results</h1>

<?php
$sql=(“SELECT * FROM users”); {

$result=mysql_query($sql) or die(“SQL Error 1: ” . mysql_error());
// get data and store in a json array
$rows=mysql_fetch_array($result);
$points[] = array(
‘Q1’ => $rows[‘question_1’],
‘Q2’ => $rows[‘question_2’],
‘Q3’ => $rows[‘question_3’]
);
}
echo json_encode($points, JSON_NUMERIC_CHECK);
?>
<?php
include ‘includes/overall/overallfooter.php’;
?>

which returns: [{“Q1″:2,”Q2″:3,”Q3″:2}]

My HTML file

<!DOCTYPE html>
<html xmlns=”http://www.w3.org/1999/xhtml&#8221; ><head>
<title>Graph</title>
<script src=”/jquery-1.10.2.js”></script>
<script type=”text/javascript” src=”js/canvasjs.min.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {
$.getJSON(“data.php”, function (result) {
var chart = new CanvasJS.Chart(“chartContainer”, {
data: [ { dataPoints: result } ]
});
chart.render();
});
});
</script>
</head>
<body>
<div id=”chartContainer” style=”width: 800px; height: 380px;”></div>
</body>
</html>

This does not render the graph. I am using Safari and develop in localhost through MAMP environment.

Thanks in advance for your reply.

[raghavendrachari]

could u help me how to set two charts on a single page (one is pie chart and another one is dynamic line graph) with php and mysql

[Anjali]

Raghavendra,

I think this thread should be of help for you.

[raghavendrachari]

$.getJSON(“livechart.php?type=drop&&node=”+node, function (result) {
dps=result;
var chart1 = new CanvasJS.Chart(“dynamicdropContainer”,{
title :{
text: “”
},
axisX: {
title: “Time”
},
axisY: {
title: “Packets Drop”,
minimum: 0,
},
data: [{
type: “column”,
indexLabelFontColor: “black”,
color: “red”,
dataPoints : dps

}]
});
chart1.render();
var xVal = result.length + 1;
var yVal = result;
var updateInterval = 1000;
var count=0;
var updateChart = function () {
var group_id=result[result.length-1].grpid+1;
$.getJSON(“newdata.php?type=drop&&node=”+node+”&&grpid=”+group_id, function (e){
if(e[0].label.length!=0){
result.push({label: e[0].label,grpid:e[0].grpid,y: e[0].y});
}

});

if(result.length-count>=250){
result.shift();
}
chart1.render();

};
setInterval(function(){updateChart()}, updateInterval);
});

here first iam getting 300 records and next from newdata.php in update function am getting one by one record which i push the record to result and render the chart .. when i did n’t use shift or aplice am getting the chart with all the records but when iam using shift or splice then my chart structs on right hand side.

could you please check is there any mistake in my code

[Anjali]

Hi,

By using shift in javascript, it just remove the items from array. Sorry but we are not able to understand that what do you mean by

chart structs on right hand side

. Just create a jsfiddle so that we can understand the problem more clearly.

—
Anjali

[kunricsi]

Hi there,

I use canvasjs to generate dynamic graphs with ajax requests, based on database rows.
I tried to generate a graph with dates, but if the ajax reply contains any new Date(..) informations, the graph rendering isn’t working. Can you provide me a working example please?

[Anjali]

Hi,

Yes, we can surely help you with that. Before that can you please post your code along with the json data so we can have a look.

__
Anjali

[pratama]

hi, i tried those example above but my chart didn’t render at all

this is my json

`<?php
header(‘Content-Type: application/json’);

session_start();

$element = $_POST[‘name’];
$year = $_POST[‘nama’];
$post = $_POST[‘name’] . $_POST[‘nama’];

include ‘koneksi.php’;

$data_points = array();

$query = mysql_query(“select nilai,tanggal from $post where bulan = 1 order by tanggal asc”);
while ($row = mysql_fetch_array($query)) {
$nila [$row[‘tanggal’]] = $row[‘nilai’];
$point = array(“x” => $row[‘tanggal’] , “y” => $row[‘nilai’]);
array_push($data_points, $point);
}

echo json_encode($data_points, JSON_NUMERIC_CHECK);
header( “refresh:5;url=chart.php” );

?>
and it returns

[{“x”:1,”y”:49},{“x”:10,”y”:”53,7″},{“x”:11,”y”:”51,3″},{“x”:12,”y”:”52,7″},{“x”:13,”y”:”46,3″},{“x”:14,”y”:”69,5″},{“x”:15,”y”:”65,3″},{“x”:16,”y”:”64,7″},{“x”:17,”y”:”60,8″},{“x”:18,”y”:”59,4″},{“x”:19,”y”:59},{“x”:2,”y”:”60,7″},{“x”:20,”y”:”45,5″},{“x”:21,”y”:”55,7″},{“x”:22,”y”:”48,4″},{“x”:23,”y”:”44,4″},{“x”:24,”y”:”49,4″},{“x”:25,”y”:”44,5″},{“x”:26,”y”:”55,3″},{“x”:27,”y”:”57,9″},{“x”:28,”y”:”51,9″},{“x”:29,”y”:”52,6″},{“x”:3,”y”:73},{“x”:30,”y”:”56,9″},{“x”:31,”y”:”69,4″},{“x”:4,”y”:”73,6″},{“x”:5,”y”:”56,3″},{“x”:6,”y”:”54,9″},{“x”:7,”y”:”60,3″},{“x”:8,”y”:”58,8″},{“x”:9,”y”:”51,3″}]

and then it’s my html

<!DOCTYPE html>
<html>
<head>
<title>JSON test</title>
<script src=”jquery/jquery.js”></script>
<script src=”canvasjs.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {

$.getJSON(“jsonparser.php”, function (result) {

var chart = new CanvasJS.Chart(“chartContainer”, {
data: [
{ type:’line’,
dataPoints: result
}
]
});

chart.render();
});
});
</script>
</head>
<body>
body test
<div id=”chartContainer” style=”width: 800px; height: 380px;”></div>

</body>
</html>

and its not rendering, is something missing or something wrong with my code???thank you in advance

• This reply was modified 10 years, 2 months ago by pratama.

[Anjali]

Hi,

In your JSON data y-value contains string data (in quotes) while CanvasJS requires numbers. So you basically need to parse those strings before assigning.

Here, is how you can do the same.

$(document).ready(function () {
var dataPoints = [];
$.getJSON("jsonparser.php", function (result) {
for (var i = 0; i < result.length; i++) {
	dataPoints.push({x: result[i].x, y: parseFloat(result[i].y)})	
};
var chart = new CanvasJS.Chart("chartContainer", {
data: [
{ type:'line',
dataPoints: dataPoints
}
]
});
chart.render();
});	
}); 

__
Anjali