Home forums Using CanvasJS How can I use PHP MySQL Dynamic data Reply To: How can I use PHP MySQL Dynamic data

#4862

Savak
Participant

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!!