How can I use PHP MySQL Dynamic data

[jo]

Hi,

I would like a help.. my code is not in php. I’m using in mvc. I would like time in x axis and a count in y axis. There would be 4 graphs plotted in the same one. Now my graph is not getting plotted when taken from db. Please help. My code is:-
function FetchDashBoardGraph() {
var input, input1;
$.ajax({
url: ‘@Url.Content(“~/Dashboard/FetchDashBoardGraph”)’,
type: ‘POST’,
responseType: “text/html”,
success: function (data) {
debugger;
input = DataCall(data);
input1 = input[0];
debugger;
var chart = new CanvasJS.Chart(“chartContainer”,
{
axisY: {
maximum: 600
},

axisX: {
interval: 3
},
legend: {
verticalAlign: “bottom”,
horizontalAlign: “center”
},
data: [input1],
//[{
// name: “Universe”,
// showInLegend: true,
// legendMarkerType: “square”,
// type: “splineArea”,
// color: “rgba(40,175,101,0.6)”,
// markerSize: 1,
// dataPoints: [
// { x: new Date(2013, 0, 1, 00, 00), label: “midnight”, y: data[0] },
// { x: new Date(2013, 0, 1, 01, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 02, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 03, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 04, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 05, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 06, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 07, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 08, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 09, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 10, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 11, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 12, 00), y: data[0], label: “noon” },
// { x: new Date(2013, 0, 1, 13, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 14, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 15, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 16, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 17, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 18, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 19, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 20, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 21, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 22, 00), y: data[0] },
// { x: new Date(2013, 0, 1, 23, 00), y: data[0] }
// ]
//},
//{
// name: “Current”,
// showInLegend: true,
// legendMarkerType: “square”,
// type: “splineArea”,
// color: “rgba(0,75,141,0.7)”,
// markerSize: 0,
// label: “”,
// dataPoints: [

// { x: new Date(2013, 0, 1, 00, 00), label: “midnight”, y: 12 },
// { x: new Date(2013, 0, 1, 01, 00), y: 10 },
// { x: new Date(2013, 0, 1, 02, 00), y: 3 },
// { x: new Date(2013, 0, 1, 03, 00), y: 5 },
// { x: new Date(2013, 0, 1, 04, 00), y: 50 },
// { x: new Date(2013, 0, 1, 05, 00), y: 200 },
// { x: new Date(2013, 0, 1, 06, 00), y: 210 },
// { x: new Date(2013, 0, 1, 07, 00), y: 198 },
// { x: new Date(2013, 0, 1, 08, 00), y: 300 },
// { x: new Date(2013, 0, 1, 09, 00), y: 211 },
// { x: new Date(2013, 0, 1, 10, 00), y: 221 },
// { x: new Date(2013, 0, 1, 11, 00), y: 240 },
// { x: new Date(2013, 0, 1, 12, 00), y: 280, label: “noon” },
// { x: new Date(2013, 0, 1, 13, 00), y: 260 },
// { x: new Date(2013, 0, 1, 14, 00), y: 170 },
// { x: new Date(2013, 0, 1, 15, 00), y: 23 },
// { x: new Date(2013, 0, 1, 16, 00), y: 28 },
// { x: new Date(2013, 0, 1, 17, 00), y: 22 },
// { x: new Date(2013, 0, 1, 18, 00), y: 10 },
// { x: new Date(2013, 0, 1, 19, 00), y: 9 },
// { x: new Date(2013, 0, 1, 20, 00), y: 6 },
// { x: new Date(2013, 0, 1, 21, 00), y: 4 },
// { x: new Date(2013, 0, 1, 22, 00), y: 12 },
// { x: new Date(2013, 0, 1, 23, 00), y: 14 }
// ]
//},
//{
// name: “Swiped In”,
// showInLegend: true,
// legendMarkerType: “square”,
// type: “splineArea”,
// color: “rgba(54,158,173,.6)”,
// markerSize: 0,
// label: “”,
// dataPoints: [

// { x: new Date(2013, 0, 1, 00, 00), label: “midnight”, y: 12 },
// { x: new Date(2013, 0, 1, 01, 00), y: 10 },
// { x: new Date(2013, 0, 1, 02, 00), y: 30 },
// { x: new Date(2013, 0, 1, 03, 00), y: 50 },
// { x: new Date(2013, 0, 1, 04, 00), y: 20 },
// { x: new Date(2013, 0, 1, 05, 00), y: 10 },
// { x: new Date(2013, 0, 1, 06, 00), y: 30 },
// { x: new Date(2013, 0, 1, 07, 00), y: 60 },
// { x: new Date(2013, 0, 1, 08, 00), y: 140 },
// { x: new Date(2013, 0, 1, 09, 00), y: 150 },
// { x: new Date(2013, 0, 1, 10, 00), y: 210 },
// { x: new Date(2013, 0, 1, 11, 00), y: 240 },
// { x: new Date(2013, 0, 1, 12, 00), y: 280, label: “noon” },
// { x: new Date(2013, 0, 1, 13, 00), y: 260 },
// { x: new Date(2013, 0, 1, 14, 00), y: 170 },
// { x: new Date(2013, 0, 1, 15, 00), y: 230 },
// { x: new Date(2013, 0, 1, 16, 00), y: 280 },
// { x: new Date(2013, 0, 1, 17, 00), y: 220 },
// { x: new Date(2013, 0, 1, 18, 00), y: 100 },
// { x: new Date(2013, 0, 1, 19, 00), y: 90 },
// { x: new Date(2013, 0, 1, 20, 00), y: 60 },
// { x: new Date(2013, 0, 1, 21, 00), y: 40 },
// { x: new Date(2013, 0, 1, 22, 00), y: 120 },
// { x: new Date(2013, 0, 1, 23, 00), y: 140 }
// ]
//},
//{
// name: “Swiped Out”,
// showInLegend: true,
// legendMarkerType: “square”,
// type: “splineArea”,
// color: “rgba(127,96,132,.6)”,
// markerSize: 0,
// label: “”,
// dataPoints: [

// { x: new Date(2013, 0, 1, 00, 00), label: “midnight”, y: 12 },
// { x: new Date(2013, 0, 1, 01, 00), y: 10 },
// { x: new Date(2013, 0, 1, 02, 00), y: 3 },
// { x: new Date(2013, 0, 1, 03, 00), y: 5 },
// { x: new Date(2013, 0, 1, 04, 00), y: 2 },
// { x: new Date(2013, 0, 1, 05, 00), y: 10 },
// { x: new Date(2013, 0, 1, 06, 00), y: 30 },
// { x: new Date(2013, 0, 1, 07, 00), y: 60 },
// { x: new Date(2013, 0, 1, 08, 00), y: 140 },
// { x: new Date(2013, 0, 1, 09, 00), y: 15 },
// { x: new Date(2013, 0, 1, 10, 00), y: 210 },
// { x: new Date(2013, 0, 1, 11, 00), y: 24 },
// { x: new Date(2013, 0, 1, 12, 00), y: 280, label: “noon” },
// { x: new Date(2013, 0, 1, 13, 00), y: 206 },
// { x: new Date(2013, 0, 1, 14, 00), y: 17 },
// { x: new Date(2013, 0, 1, 15, 00), y: 23 },
// { x: new Date(2013, 0, 1, 16, 00), y: 28 },
// { x: new Date(2013, 0, 1, 17, 00), y: 22 },
// { x: new Date(2013, 0, 1, 18, 00), y: 10 },
// { x: new Date(2013, 0, 1, 19, 00), y: 90 },
// { x: new Date(2013, 0, 1, 20, 00), y: 60 },
// { x: new Date(2013, 0, 1, 21, 00), y: 40 },
// { x: new Date(2013, 0, 1, 22, 00), y: 12 },
// { x: new Date(2013, 0, 1, 23, 00), y: 14 }
// ]
//}
//]
});
chart.render();
},
error: function () {
RedirectToErrorPage();
}
});
}

function DataCall(details) {
var data = [];
var dataSeries = {
name: “Swiped Out”,
showInLegend: true,
legendMarkerType: “square”,
type: “splineArea”,
color: “rgba(127,96,132,.6)”,
markerSize: 0,
label: “”,
};
var dataPoints = [];
for (var i = 0, j = 0; i < details.length; i = i + 2, j++) {
y = details[i];
dateTime = new Date(2013, 0, 1, j, 00);
//dateTime.setMilliseconds(dateTime.getMilliseconds() + i);
//dateTime.setSeconds(dateTime.getSeconds() + i);
//dateTime.setMinutes(dateTime.getMinutes() + i);
//dateTime.setHours(dateTime.getHours() + i);
//dateTime.setDate(dateTime.getDate() + i);
//dateTime.setMonth(dateTime.getMonth() + i);
//dateTime.setFullYear(dateTime.getFullYear() + i);

dataPoints.push({
//x: (i+1) % 50 === 0 ? dateTime.getTime() : dateTime,
//x: i + 345345,
x: dateTime.toLocaleString(),
///x: i,
y: y
});
}
dataSeries.dataPoints = dataPoints;
data.push(dataSeries);
debugger;
return data;
}

[kthilagarajan]

Hi This is my code, I am using Ajax for Displaying Chart

Below is index.php (I am trying to display chart in this page)

$(document).ready(function() {
		var fromDate = document.getElementById('fromDate').value;
		var toDate =  document.getElementById('toDate').value;
		
		$("#rangeButton").click(function(){
			var clickBtnValue = $(this).val();
			//alert(clickBtnValue);
			//alert(fromDate + ' '+toDate);
			var ajaxurl = 'dbconnect.php',
			data =  {'action': clickBtnValue, 'from': fromDate, 'to' : toDate,  dataType: 'json'};
			
			$.post(ajaxurl, data,  function (response) {
				alert(response);			
				var chart = new CanvasJS.Chart("chartContainer", {
					animationEnabled: true,
                    data: [
                        {
							type: "spline",
                            dataPoints: response
                        }
                    ]
                });

                chart.render();
		}); 
	});

Below is dbconnect.php (Fetching Data from DB)

$data_points = array();
	while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
	{	
		$point = array('label' => $row['date'] , 'y' => $row['value']);
		array_push($data_points, $point); 
		
	} 
	echo json_encode($data_points, JSON_NUMERIC_CHECK);

The response which I received is

[{"label":"2015-09-13","y":"10"},{"label":"2015-09-14","y":"20"},{"label":"2015-09-10","y":"70"},{"label":"2015-09-11","y":"80"}]

Everything was correct, but chart was not displayed. When I give static values, it is working! How to solve this issue?

[manoch]

to kthilagarajan
The same problem i think because options JSON_NUMERIC_CHECK has not work for MyComnputer /localhost or appserv
you can test code http://phptester.net/ with example code

<?php
$stack = array("100",  "200");
array_push($stack, "300", "400");
//print_r($stack);

echo json_encode($stack,JSON_NUMERIC_CHECK);//this line for my computer is error
echo json_encode($stack,128);//same echo json_encode($stack); 

you will see different output.
sory for english word .

Good luck.
Manoch

[hrahul]

I want my graph to automatically refresh data as it comes in to the database. However, i am getting a blank screen with the following html code. Could you please correct the error?
file t1.php

<?php

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

$con = mysqli_connect("localhost","root","","test1");

// Check connection
if (mysqli_connect_errno($con))
{
    echo "Failed to connect to DataBase: " . mysqli_connect_error();
}else
{
    $data_points = array();
    
    $result = mysqli_query($con, "SELECT * FROM val");
    
    while($row = mysqli_fetch_array($result))
    {        
        $point = array("label" => $row['rnumber'] ,"y" =>$row['water_level']);
        
        array_push($data_points, $point);        
    }
	
    
    echo json_encode($data_points, JSON_NUMERIC_CHECK);
}
mysqli_close($con);

?>

file t2.html

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script src="jquery.js"></script>
    <script src="canvasjs.min.js"></script>

    <script type="text/javascript">
	var updateInterval = 10;
	var dps;
        $(document).ready(function () {

            $.getJSON("t1.php", function (result) {
dps=result;
                var chart = new CanvasJS.Chart("chartContainer", {
                    data: [
                        {
                            dataPoints: dps
                        }
                    ]
                });

                chart.render();
            });
			var updateChart = function () {
$.getJSON(“t1.php”, function (result) {

chart.options.data[0].dataPoints = result;

});

chart.render();
};

setInterval(function(){updateChart()}, updateInterval);
        });
    </script>
</head>
<body>

    <div id="chartContainer" style="width: 800px; height: 380px;"></div>

</body>
</html>

[jhe_24]

I’m interested in your graphs sir .. I would like to ask for some help on how to incorporate those graphs with a sql database .. i always had problems in fetching the json file and the graph wont show .. please help ..

[Sanjoy]

Without knowing which server side technology you are using, we can’t direct you. So, can you please post your sample Json data along with code

[elvisximenes]

Hi Sunil,
Refering to the thread #3669 , i am only get one line for line chart.
based on my JSON data from MySQL below: how can i display into in multi series line charts.
– The report_date will be in x axis
– submited,approved,declined will be displayed in three different lines.

[
{
“report_date”:”2016-02-02″,
“submited”:1,
“approved”:”1″,
“declined”:”0″
},
{
“report_date”:”2016-02-03″,
“submited”:2,
“approved”:”0″,
“declined”:”1″
},
{
“report_date”:”2016-02-17″,
“submited”:1,
“approved”:”0″,
“declined”:”0″
}
]

When i do below, i only get one line.
$result= getReportStatistic($startDate,$endDate);
$data_points = array();
foreach($result as $row){
$point = array(“label”=>$row[‘report_date’],”y”=>$row[‘submited’]);
array_push($data_points, $point);
}
echo json_encode($data$data_points, JSON_NUMERIC_CHECK);

//AJAX
success: function(data){
var result = $.parseJSON(data);
var columnchart = new CanvasJS.Chart(“chartContainer”, {
title:{
text: “Overall report statistic”
},
axisX:{
gridColor: “Silver”,
tickColor: “silver”,
valueFormatString: “DD/MMM”
},
data: result
});
columnchart.render();
}

Really Appreciated for your help.
Best Regards,
Elvis Ximenes

[ericsonweah]

Sunil Urs,
Seriously I am so amazed on how quickly, precised, and prompt you are in answering people questions and concerns. Thanks again for all that you do. I greatly appreciate it. This code you provided works perfectly for me and I am more grateful for your help. Please do not hesitate to let me know if there is anything I can do to help. Thanks again for everything.

[Sanjoy]

elvisximenes,

Can you please post sample of your JSON data.
And for showing 3 lines you have to pass JSON data in the CanvasJS data format with all the 3 dataSeries from server side. Similar as you have done for ‘submited’.

• This reply was modified 7 years, 10 months ago by Sanjoy.

[dantelo]

Hello sir,
any example get data from database for Live Random Data Charts
https://canvasjs.com/html5-javascript-dynamic-chart/

Sorry sir, I’m newbie

[Sanjoy]

For displaying live data from database you need to create

1. A PHP service (or any other server side technology) that returns data in JSON format to CanvasJS,
2. HTML page that does AJAX request after certain interval to the server and fetches the data. After getting the data, it will render the updated chart.

Here is a similar example to implement the same in client side.

Also refer this thread.

[quocnamvnn.vnn]

My code has been drawing diagrams. I’m wondering how that can be called the following statement 10s sql queries to add new data to programs like online. I have the files:
1 / Data_Update.php – PHP to return JSON Data Service
2 / Dso_Chart_Update.php – Fetch Data and render Chart
3 / Chart_Update.php – HTML page called 2 files on.
I want to fill online chart by recalling Data_Update.php files for new data and adding this data to have to draw additional datapoint. Can you help me ?

I did refer to the link: http://jsfiddle.net/canvasjs/qajfpzeL/
I find the program file recall jsonData1.json after 2s. But this jsonData1.json update file data?
Sorry my english is not good.

[Vishwas R]

Yes, its possible to update data for every ‘n’ seconds (10seconds in your case). Initially you can render chart with first set of data from an external JSON or the data received from the php service. And while updating you can fetch data from new source where new set of data is present and re-render the chart with new set of data. Here is an example.

[quocnamvnn.vnn]

Thanks Vishwas R … Done :D

[varundutta]

Hi Sunil,

First of all thank you so much and best wishes for your efforts and creating such a beautiful chart utility.
I am new to php however I managed to use your peice of code above to create charts from mysql. But I have a specific requirement of showing chart from mysql and also data used in that chart in a a HTML table. I got success to create chart using above code however struggling in the second part.

I am using 3 file, Main.php, data.php and create_chart.js. In data.php code for pulling mysql data n encoding it to json is used. Then I have used te json data in JS file to create chart. In my main php file I am calling my JS file to render chart. Below the chart I want to show the HTML table. So i am trying to call data.php file using “include (‘data.php’)”. But when I do so, due to << echo json_encode … >> line the array is also displaying on the page which I don’t want. I also don’t want to run same SQL query 2 times to get the chart and data.

Please help me on this. That will be great help.