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

Tagged: , , ,

This topic contains 44 replies, has 23 voices, and was last updated by  varundutta 3 months, 1 week ago.

Viewing 15 posts - 1 through 15 (of 45 total)
  • Author
    Posts
  • #3650

    naavin005
    Participant

    Hi I’m very interesting About Canvas graph can i know how can I plot graph with Dynamic Mysql data Using PHP. Can you plz give me a simple example

    #3668

    Sunil Urs
    Keymaster

    Hi Naveen,

    Thanks for your interest in CanvasJS. We’ll get back to you with and example in some time…

    Sunil Urs,
    Team CanvasJS

    #3669

    Sunil Urs
    Keymaster

    Naveen,

    Here is how you can display MySQL data in CanvasJS,

    1. Create a PHP service that returns data in JSON format.
    2. HTML page that does AJAX request to the server and fetches the data. After getting the data, it renders a Chart.

    Below is the sample code

     

    1. PHP Service to return JSON Data

    <?php
    
    header('Content-Type: application/json');
    
    $con = mysqli_connect("127.0.0.1","user","password","canvasjssampledb");
    
    // 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 sales");
        
        while($row = mysqli_fetch_array($result))
        {        
            $point = array("label" => $row['product'] , "y" => $row['total_sales']);
            
            array_push($data_points, $point);        
        }
        
        echo json_encode($data_points, JSON_NUMERIC_CHECK);
    }
    mysqli_close($con);
    
    ?>

    2. HTML Page to Fetch Data and render Chart

    <!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head>
        <title></title>
        <script src="jquery.js"></script>
        <script src="canvasjs.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>
    
    #4048

    naavin005
    Participant

    Thanks for your kind replay. Do canvas js will work on IE7 and IE8

    #4049

    naavin005
    Participant

    Canvas js not working on IE8, what can i do to work on IE8 please provide me the solution

    #4050

    Sunil Urs
    Keymaster

    Naavin,

    CanvasJS requires a minimum of IE9. Older versions are not supported.

    Update : IE8- is supported in v1.3 and above.

    Sunil Urs,
    Team CanvasJS

    • This reply was modified 3 years ago by  Sunil Urs.
    #4632

    marcobpasquali
    Participant

    Hi,

    I tried to do what you describe here, but my graph is not reloading.. I’m putting the code here:

    data.php:
    <?php

    header(‘Content-Type: application/json’);

    //declaracao de servidores
    $server = “10.7.5.31”;
    $user = “root”;
    $senha = “root”;
    $bd = “me-rpc”;

    mysql_connect($server, $user, $senha) or die(mysql_error());
    mysql_select_db($bd) or die(mysql_error());

    $data_points = array();

    $result1 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘curitiba’ AND isLocked = 0 “)
    or die(mysql_error());

    $result2 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘foz’ AND isLocked = 0 “)
    or die(mysql_error());

    $result3 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘cascavel’ AND isLocked = 0 “)
    or die(mysql_error());

    $result4 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘maringa’ AND isLocked = 0 “)
    or die(mysql_error());

    $result5 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘paranavai’ AND isLocked = 0 “)
    or die(mysql_error());

    $result6 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘pontagrossa’ AND isLocked = 0 “)
    or die(mysql_error());

    $result7 = mysql_query(“SELECT tamanho, fileName, porcentagem FROM londrina WHERE status = 5 AND origem = ‘guarapuava’ AND isLocked = 0 “)
    or die(mysql_error());

    while($row = mysql_fetch_array( $result1 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result2 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result3 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result4 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result5 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result6 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    while($row = mysql_fetch_array( $result7 )) {
    if($row[‘fileName’] != null){
    $point = array(“label” => $row[‘fileName’] , “y” => ($row[‘porcentagem’]/$row[‘tamanho’]*100));
    array_push($data_points, $point);
    }else{
    $point = array(“label” => “slot livre” , “y” => 0);
    array_push($data_points, $point);
    }
    }

    mysql_close();

    echo json_encode($data_points, JSON_NUMERIC_CHECK);

    ?>

    graph.html:

    <!DOCTYPE html>
    <head>

    <title>graph</title>

    <script src=”jquery.js”></script>

    <script src=”canvasjs.js”></script>

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

    $.getJSON(“data.php”, function (result) {
    dps = result;
    var chart = new CanvasJS.Chart(“chartContainer”, {
    title:{
    text: “RECEBIMENTO DE MATERIAIS”,
    },
    axisY:{
    minimum: 0,
    maximum: 100
    },
    data: [

    {
    type: “stackedBar”,
    dataPoints: dps

    }

    ]

    });

    chart.render();

    });

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

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

    });

    </script>

    </head>

    <body>

    <div id=”chartContainer” style=”height: 300px; width: 100%;”>

    </body>

    </html>

    There´s something wrong? In first time the chart load, but the reload function not.

    Thanks

    #4636

    Sunil Urs
    Keymaster

    Marco,

    Can you please post the JSON data that the service is returning so that I can figure out what the problem is. Do make sure that the x & y values being returned are not enclosed in quotes like “5”.


    Sunil Urs

    #4641

    timverbruggen
    Participant

    I just use .php .
    After connecting to the database i do something like:

    mysql_select_db($database_DBconnect, $DBconnect);
    $query_rsHistWKK = “SELECT * FROM table”;
    $rsHistWKK = mysql_query($query_rsHistWKK, $DBconnect) or die(mysql_error());
    $row_rsHistWKK = mysql_fetch_assoc($rsHistWKK);
    $totalRows_rsHistWKK = mysql_num_rows($rsHistWKK);

    $Grolleman_op = ”;
    $Grolleman_af = ”;
    $x = 0;
    $komma = “, “;
    do {
    $x = $x+1;
    $datum = strtotime( $row_rsHistWKK[‘DatumTijd’] );

    $jaar = date(“Y”, $datum );
    $maand = date(“m”, $datum );
    $maand = $maand -1 ; // in Javascript is de waarde van een maand 1 lager dan in php
    $maandtekst = date(“F”, $datum );
    $dag = date(“j”, $datum );
    $dagsoort = date(“l”, $datum );
    $uur = date(“G”, $datum );
    $minuut = date(“i”, $datum );
    $seconde = date(“s”, $datum );
    $grollemanop = 1900- $row_rsHistWKK[‘Grolleman_Trafo1’]- $row_rsHistWKK[‘Grolleman_Trafo2’];
    $grollemanaf = $row_rsHistWKK[‘Grolleman_kW_af’];
    $grolleman_max = max($grolleman_max,$grollemanop,$grollemanaf);
    $grolleman_min = min($grolleman_min,$grollemanop,$grollemanaf);
    $Grolleman_op = $Grolleman_op.” { x: new Date(“.$jaar.$komma.$maand.$komma.$dag.$komma.$uur.$komma.$minuut.$komma.$seconde.”), y: “.$grollemanop.”},”;
    $Grolleman_af = $Grolleman_af.” { x: new Date(“.$jaar.$komma.$maand.$komma.$dag.$komma.$uur.$komma.$minuut.$komma.$seconde.”), y: “.$grollemanaf.”},”;

    } while ($row_rsHistWKK = mysql_fetch_assoc($rsHistWKK))

    #4644

    marcobpasquali
    Participant

    Sunil Urs,

    my return is:

    [{“label”:”PASS 2B (LANCHE).dif”,”y”:19.825327510917},{“label”:”CTA CRIANCAS VIROS com artes.dif”,”y”:11.396601740572},{“label”:”slot livre”,”y”:0},{“label”:”slot livre”,”y”:0},{“label”:”slot livre”,”y”:0},{“label”:”slot livre”,”y”:0},{“label”:”slot livre”,”y”:0}]

    #4645

    Sunil Urs
    Keymaster

    Marco,

    What is going wrong is that you are assigning a new array to dps while dataPoints is still pointing to old one. So instead you should either modify existing dps array or assign a totally new array to dataPoints.

    1st Approach:

    var updateChart = function () {
    $.getJSON(“data.php”, function (result) {

    dps.splice(0, dps.length);

    $.each(result, function (index, value) {
    dps.push(value);
    });
    });

    chart.render();
    };

    2nd Approach:

    var updateChart = function () {
    $.getJSON(“data.php”, function (result) {

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

    });

    chart.render();
    };

    #4751

    photon77
    Participant

    I am an EE Prof., enjoying this package. Thanks for a great site with lots of examples. I am looking to take data from mysql which is generated via sensor enabled Arduinos sending info (temp, humidity, ect) regularly to the mysql. I have the Arduino to mysql working, and can extract the data from mysql and plot with another package, but can’t render the data in canvasjs. To get comfortable with canvasjs I have made a couple of simple files to test, and am stuck. For simplicity, have made a table with just three ints: id, x, and y. The following PHP seems to work. The table “testing” just has three columns: id, x, and y which are all set to int, and id is set to autoincrement (using phpmyadmin).
    <?php
    header(‘Content-Type: application/json’);
    $con=mysqli_connect(“localhost”, “cjsuser”, “temp2013”, “canvasjsdb”);
    // 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 testing”);
    while($row = mysqli_fetch_array($result))
    {
    $point = array(“label” => $row[‘x’] , “y” => $row[‘y’]);
    array_push($data_points, $point);
    }
    echo json_encode($data_points, JSON_NUMERIC_CHECK);
    }
    mysqli_close($con);
    ?>
    This is live at: http://www.capegreenenergy.com/dev/sunilJSON.php
    It returns: [{“label”:1,”y”:10},{“label”:2,”y”:20},{“label”:3,”y”:15}]

    However, the following doesn’t render, and I think I am missing something obvious.
    <html>
    <head>
    <title>JSON TEST</title>
    <script src=”jquery.js”></script>
    <script src=”/js/canvasjs.js”></script>
    <script type=”text/javascript”>
    $(document).ready(function () {
    $.getJSON(“sunilJSON.php”, function (result) {
    var chart = new CanvasJS.Chart(“chartContainer”, {
    data: [ { dataPoints: result } ]
    });
    chart.render();
    });
    });
    </script>
    </head>
    <body>
    <h2>OK NOW IN THE BODY</h2>
    <div id=”chartContainer” style=”width: 800px; height: 380px;”></div>
    <h2>OK this is after chartContainer</h2>
    </body>
    </html>
    This is live at:
    http://www.capegreenenergy.com/dev/sunilJSON.html
    and as you can see it is live but the chart does not render.

    I used a simple example from your site to make sure my directory structure is ok (I have /dev and /js at the same level of hierarchy). This one works fine:
    http://www.capegreenenergy.com/dev/simpleChart.html

    <html>
    <head>
    <script type=”text/javascript”>
    window.onload = function () {
    //function () {

    var chart = new CanvasJS.Chart(“chartContainer”, {

    title:{
    text: “Fruits sold in First Quarter”
    },
    data: [//array of dataSeries
    { //dataSeries object

    /*** Change type “column” to “bar”, “area”, “line” or “pie”***/
    type: “column”,
    dataPoints: [
    { label: “banana”, y: 18 },
    { label: “orange”, y: 29 },
    { label: “apple”, y: 40 },
    { label: “mango”, y: 34 },
    { label: “grape”, y: 24 }
    ]
    }
    ]
    });

    chart.render();
    }
    </script>
    <script type=”text/javascript” src=”/js/canvasjs.js”></script>
    </head>
    <body>
    Thanks again for a cool package and thanks in advance for helping me get unstuck 🙂

    • This reply was modified 3 years, 5 months ago by  photon77.
    #4755

    Sunil Urs
    Keymaster

    Hi,

    I just checked the links. jquery is not referenced properly in the given URL. Please fix the same and it should work properly…

    http://www.capegreenenergy.com/dev/sunilJSON.html

    #4756

    photon77
    Participant

    OK now my ignorance is really going to show! What does it mean to have jquery properly referenced in the URL? Thanks again, very much. Jack Adams

    #4757

    Sunil Urs
    Keymaster

    jquery's path is wrong. You can see this using Chrome Developers Tools or Firebug.

    Please replace
    <script src="jquery.js"></script>

    with (google hosted jquery script)
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>

    Do let me know if it worked.

Viewing 15 posts - 1 through 15 (of 45 total)

You must be logged in to reply to this topic.