Home Forums Chart Support How can I use PHP MySQL Dynamic data

How can I use PHP MySQL Dynamic data

Viewing 8 posts - 46 through 53 (of 53 total)
  • #20422

    Hello sir, I am storing sensor data in mysql database ,now I want to use canvasJS to show realtime data from mysql database on web page.
    Jquery code for updating chart data

    function fData(){
        var lastid= $('#lastId').val(); 
       $.ajax({
                            url: httppath+"pages/fetchData",
                            type: "post",
                            data:{pId:$('#pid').val(),'lastId':lastid},
                            dataType:"json",
                            success: function (res) {
                                 if(res['Pt']!=='' || res['Pt']!=" "){
                                   $('#lastId').val(res["Pt"]['id']);
                                     updateChart(res['Pt']);
                                 }
                            },
                            error: function(jqXHR, textStatus, errorThrown) {
    //                           console.log(textStatus, errorThrown);
                            }
                        });
    }   
       var dps = []; // dataPoints
       var xVal;
       var yVal;	
       var chart = new CanvasJS.Chart("chartContainer",{
          	title :{
          		text: "Live Data"
          	},
          	axisX: {						
          		title: "Axis X Title"
          	},
          	axisY: {						
          		title: "Pulse Rate"
          	},
          	data: [{
          		type: "line",
          		dataPoints : dps
          	}]
          });
    
          chart.render();
          
          var updateChart = function (data) {
          	yVal = data['sensor_value'];
          	dps.push({x: xVal,y: yVal});
          	console.log(dps);
            xVal++;
            if (dps.length >  10 )
          	{
          		dps.shift();				
          	}
          	chart.render();		
    	// update chart after specified time. 
          };
    #20427

    @connect-javed001,

    Please check this sample project to create dynamic charts using data from MySQL database in PHP. Also, please refer to this documentation page to create a Live Updating Charts from JSON API & AJAX.
    Dynamic Chart with Data from external JSON

    ___________
    Indranil Deo,
    Team CanvasJS

    #23818

    I have a somewhat similiar case. I want to create a dynamic Chart from an Mysql-DB

    Unfortunatelly, I get the Msg “TypeError: chart is undefined”
    I am “only” a Graphic Designer and no programmer :()

    =============================================================================================================
    SCRIPT
    =============================================================================================================

    <!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head>
        <title></title>
        <script src="https://code.jquery.com/jquery-3.3.1.js" integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60=" crossorigin="anonymous"></script>
        <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script>
    
      <script type="text/javascript">
    	var updateInterval = 100;
    	var chart;
    	var dps;
    	$(document).ready(function () {
    
    		$.getJSON("testdata.php", function (result) {
    			dps = result;
    			var chart = new CanvasJS.Chart("chartContainer", {
    				title:{
    					text: "Test",
    				},
    				axisY:{
    					minimum: 0,
    					maximum: 1000
    				},
    				data: [
    					{
    						type: "stackedBar",
    						dataPoints: dps
    					}
    				]
    			});
    			
    		chart.render();
    	});
    
    var updateChart = function () {
    	$.getJSON("testdata.php", function (result) {
    		dps.splice(0, dps.length);
    		$.each(result, function (index, value) {
    			dps.push(value);
    		});
    	});
    
    chart.render();
    };
    
    setInterval(function(){updateChart()}, updateInterval);
    
    });
    
    </script> 
    </head>
    <body>
    BODY
        <div id="chartContainer" style="width: 800px; height: 380px;"></div>
    
    </body>
    </html>

    =============================================================================================================
    DATA
    =============================================================================================================

    <?php
    
    header('Content-Type: application/json');
    
    $con = mysqli_connect("localhost","wp1","QPZBtAe7","wp1");
    
    // 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 id,Laserschneider_Druckluft_Volumenstrom_Nm3h FROM blb ORDER BY id DESC LIMIT 500");
        
        while($row = mysqli_fetch_array($result))
        {        
            $point = array("label" => $row['id'] , "y" => $row['Laserschneider_Druckluft_Volumenstrom_Nm3h']);
            array_push($data_points, $point);        
        }
        
        echo json_encode($data_points, JSON_NUMERIC_CHECK);
    }
    mysqli_close($con);
    #23824

    @gereby,

    The issue seems to be with the scope of the variables – chart, please refer this stackoverflow thread for more info on scope of a variable in JavaScript. Please find the working code-snippet below.

    var updateInterval = 100;
    var chart;
    var dps;
    $(document).ready(function() {
    	$.getJSON("testdata.php", function(result) {
    		dps = result;
    		chart = new CanvasJS.Chart("chartContainer", {
    			title: {
    				text: "Test",
    			},
    			axisY: {
    				minimum: 0,
    				maximum: 1000
    			},
    			data: [{
    				type: "stackedBar",
    				dataPoints: dps
    			}]
    		});
    
    		chart.render();
    	});
    
    	var updateChart = function() {
    		$.getJSON("testdata.php", function(result) {
    			dps.splice(0, dps.length);
    			$.each(result, function(index, value) {
    				dps.push(value);
    			});
    		});
    
    		chart.render();
    	};
    
    	setInterval(function() {
    		updateChart()
    	}, updateInterval);
    });

    Please take a look at this php file for complete code.


    Vishwas R
    Team CanvasJS

    #25145

    Hi, I am trying to implement CanvasJS in my code so that I can display my mysql data in different charts and apply filter later like Select results Year wise, Department wise

    But I am getting the plain text displayed in webpage when I follow this code like this :

    <!DOCTYPE html>
    <html>
    <head>
    </head>
    <body>
    [{"label":"Sample size calculation","y":42},{"label":"Statistical analysis","y":206},{"label":"Data management","y":25},{"label":"Research method","y":7},{"label":"Writing a manuscript","y":2},{"label":"Health technology assessment \/ Economic Evaluation","y":7},{"label":"Data Logical","y":5},{"label":"Others","y":2}]<div id="chartContainer" style="height: 380px; width: 800; margin: 0px auto;"></div>
    <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script>
        
        <script src="jquery.js"></script>
        <script src="canvasjs.js"></script>
    
        <script type="text/javascript">
            $(document).ready(function () {
    
                $.getJSON("getjson.php", function (result) {
    
                    var chart = new CanvasJS.Chart("chartContainer", {
                        data: [
                            {
                                //type: 'pie',
                                dataPoints: result
                            }
                        ]
                    });
    
                    chart.render();
                });
            });
        </script>
    </body>
    </html>

    I have two php files like you suggested:
    1. getjson.php
    2. render.php

    1. getjson.php

    <?php
    
    header('Content-Type: application/json');
    
    include "console/connect_db.php";
    
        $data_points = array();
        $sql = "SELECT COUNT(*) as count,a.id_skill,s.skill FROM appointment a,skills_data s WHERE 
            a.id_skill=s.id_skill and a.ap_status ='complete' GROUP BY `id_skill`";
        $result = $conn->query($sql);
        while($row = $result->fetch_assoc())
        {
            $point = array("label" => $row['skill'] , "y" => $row['count']);
            
            array_push($data_points, $point);
        }
        
        echo json_encode($data_points, JSON_NUMERIC_CHECK);
    
    ?>

    2. render.php

    <!DOCTYPE html>
    <html>
    <head>
    </head>
    <body>
    <?php
    require "getjson.php";
    ?>
    <div id="chartContainer" style="height: 380px; width: 800; margin: 0px auto;"></div>
    <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script>
        
        <script src="jquery.js"></script>
        <script src="canvasjs.js"></script>
    
        <script type="text/javascript">
            $(document).ready(function () {
    
                $.getJSON("getjson.php", function (result) {
    
                    var chart = new CanvasJS.Chart("chartContainer", {
                        data: [
                            {
                                //type: 'pie',
                                dataPoints: result
                            }
                        ]
                    });
    
                    chart.render();
                });
            });
        </script>
    </body>
    </html>
    #25171

    @ganesh,

    Removing the following line seems to be working fine require "getjson.php";. If the issue still persists, kindly share sample project along with sample database over Google Drive or Onedrive so that we can run it locally at our end, understand the issue better and help you out.

    ——
    Manoj Mohan
    Team CanvasJS

    #35635

    can we use dates in x-axis in dynamic charts

    #35652

    @m-usman,

    Yes, you can use datetime in x-axis. In order to use datetime in x-axis, you need to first convert PHP date to timestamp using strtotime and then convert PHP timestamp to JavaScript timestamp by multiplying it with 1000.

    $phpDate = date("Y-m-d h:i:sa");
    $phpTimestamp = strtotime($phpDate);
    $javaScriptTimestamp = $phpTimestamp * 1000;

    You can refer to this thread for more information on using datetime on x-axis in PHP. Also, you can check out this gallery page for an example on using JavaScript timestamp in x-axis in PHP.

    —-
    Manoj Mohan
    Team CanvasJS

Viewing 8 posts - 46 through 53 (of 53 total)

You must be logged in to reply to this topic.