Home forums Using CanvasJS Issues retrievng data from MySql

This topic contains 27 replies, has 3 voices, and was last updated by  andres.chandia 2 years ago.

Viewing 15 posts - 1 through 15 (of 28 total)
  • Author
    Posts
  • #7421

    andres.chandia
    Participant

    Hi there, I have followed one of the answers you gave about retrieving data from MySql DB and I did it successfully, but I have some issues with some of the graphics, all of them regarding labels.
    First, here you can find my graphics: http://parles.upf.edu/llocs/adljc/grafics/
    You will see in this page 5 little graphics, clicking on the magnifying glass you can see a larger version of them.
    1st one, no problem at all.
    2nd, “Tipus de recursos” (Pie): no labels at all, here’s the code for this graphic:
    file graf_tipus_recursos.php (not all the code, only the part that matters)

           <script src="jquery.js"></script>
            <script src="canvasjs.js"></script>
            <script src="tipus_recursos.js"></script>
    </head>
    <body>
    <div id="chartContainer2" style="width: 1200px; height: 700px;" ></div>

    file tipus_recursos.js

    $(document).ready(function () {
                $.getJSON("tipus_recursos.php", function (result) {
                    var chart = new CanvasJS.Chart("chartContainer2", {
                        data: [
                            {
                                type: "pie",
                                dataPoints: result
                            }
                        ]
                    });
                    chart.render();
                });
            });

    file tipus_recursos.php

    <?php
    header('Content-Type: application/json');
    $con = mysqli_connect("localhost","user","password","database");
    if (mysqli_connect_errno($con))
    {
        echo "Failed to connect to DataBase: " . mysqli_connect_error();
    }else
    {
        $data_points = array();
        $result = mysqli_query($con, "SELECT Tipus_1 AS Tipus, COUNT(*) AS 'Count' FROM values GROUP BY Tipus_1 ORDER BY Count");
        while($row = mysqli_fetch_array($result))
        {
            $point = array("label" => $row['Tipus_1'] , "y" => $row['Count']);
            array_push($data_points, $point);
        }
        echo json_encode($data_points, JSON_NUMERIC_CHECK);
    }
    mysqli_close($con);
    ?>

    There is no difference between the queries of the first graphic and this one, except for the field requested.
    It happens the same with graphics 3 “Recursos lingüístics” and 4 “Recursos jurídics” with the difference that in these two the tags take numeric values while they should be names.

    5th graphic, “Tipus de suport”: well this is kind of weird, at the little version labels can be seen, overlapping each other, but they are there, while viewing the large version, labels at the x axis are replaced by numbers but they remain when hovering the columns.

    So here I add a question; My table is somthing like this:
    | id | Title | Support |
    ——————————–
    | 1 | First | Internet |
    | 2 | Second | Paper |
    | 3 | Third | CD |
    | 4 | Fourth | Paper,CD |
    | 5 | Fifth | Internet,Paper |

    So I would like to render a Graph with 3 columns only, Internet, Paper, CD
    for each column the query should be like:

    $query1: "SELECT Support, COUNT(*) AS 'Count1' FROM table WHERE Support LIKE '%paper%';
    $query2: "SELECT Support, COUNT(*) AS 'Count2' FROM table WHERE Support LIKE '%internet%';
    $query3: "SELECT Support, COUNT(*) AS 'Count3' FROM table WHERE Support LIKE '%CD%';

    So the question is, how do I make fit all of this to have the required graph, I’ve already tried a lot of combinations unsuccessfully, I would appreciatea a lot if you can heklp me on this….

    Thanks a lot

    #7446

    Anjali
    Participant

    Andres,

    In order to show labels you need to set label property of dataPoints. Here is the documentation on the same.

    __
    Anjali

    #7449

    andres.chandia
    Participant

    Thanks for answering, but I only see parameters for static data, how do I apply this to dynamic data, I mean, retrieved from mysql dtabase.
    and thanks again man.

    #7450

    Anjali
    Participant

    Andres,

    You just need to parse and modify your data as required. Below is an example.

    var dataPoints = [];
    	$.getJSON("tipus_recursos.php", function (result) {
    		for(var i = 0; i <= result.length-1; i++) {
    			dataPoints.push({label: result[i].Support, y: parseInt(result[i].y)});
    		}
    	var chart = new CanvasJS.Chart("chartContainer", {
    		data: [
    		{
    			dataPoints: dataPoints
    		}
    		]
    	});
    	chart.render();
    	});

    __
    Anjali

    #7451

    andres.chandia
    Participant

    No luck, it renders in the same way, without the lables…

    here is the query:

    $data_points = array();
        $result = mysqli_query($con, "SELECT Tipus_1 AS Tipus, COUNT(*) AS 'Count' FROM values GROUP BY Tipus_1 ORDER BY Count");
        while($row = mysqli_fetch_array($result))
        {
            $point = array("label" => $row['Tipus_1'] , "y" => $row['Count']);
            array_push($data_points, $point);
        }
        echo json_encode($data_points, JSON_NUMERIC_CHECK);
    

    here is the js file:

    var dataPoints = [];
            $.getJSON("tipus_recursos.php", function (result) {
                    for(var i = 0; i <= result.length-1; i++) {
                            dataPoints.push({label: result[i].Tipus_1, y: parseInt(result[i].y)});
                    }
            var chart = new CanvasJS.Chart("chartContainer2", {
                    data: [
                    {
                            type: "pie",
                            dataPoints: dataPoints
                    }
                    ]
            });
            chart.render();
            });
    #7455

    Anjali
    Participant

    Andres,

    We looked into your JSON data and labels are null because of which it is not showing in second, third and fourth chart. When label is null chart shows x-axis value at that point.

    Here is the JSON data used by your second chart :

    [{"label":null,"y":1},{"label":null,"y":45},{"label":null,"y":85}]

    In case of fifth chart labels are overlapping because they are very long. You can avoid the same by wrapping labels as shown in thedocumentation.

    __
    Anjali

    #7456

    andres.chandia
    Participant

    Thanks Anjali,
    I have tried before with another graphic generator http://parles.upf.edu/llocs/adljc/grafics.old/ and if you check those you will see that x axis labels are generated, not pretty well, but the labels are there because the data is actually there.
    At the pie graphic you will see the labels have encoding issues, but they are there, could this be the reason for canvas not showing labels, my data is in utf-8 and words have accents…

    Queries are the same, it’s only the way of generating the graphic that is different, it’s a php library: http://www.ebrueggeman.com/phpgraphlib

    Actually, both graphics (phplib and canvasjs) are currently generated form the exact same data, I preffer canvas because it is visually better than the other.

    So why could be json showing as null when the fields are actually not.

    #7458

    andres.chandia
    Participant

    Just in case, i’m adding the table structure

    — phpMyAdmin SQL Dump
    — version 4.2.6deb1
    http://www.phpmyadmin.net

    — Servidor: localhost
    — Tiempo de generación: 03-11-2014 a las 12:23:46
    — Versión del servidor: 5.5.40-0ubuntu1
    — Versión de PHP: 5.5.12-2ubuntu4.1

    SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
    SET time_zone = “+00:00”;

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;


    — Base de datos: database

    — ——————————————————–


    — Estructura de tabla para la tabla values

    CREATE TABLE IF NOT EXISTS values (
    Id int(6) NOT NULL,
    Titol varchar(500) DEFAULT NULL,
    Autor varchar(200) DEFAULT NULL,
    Any_publicacio int(4) NOT NULL,
    Pub_prod varchar(200) DEFAULT NULL,
    Lloc_publicacio varchar(200) DEFAULT NULL,
    Suport varchar(200) DEFAULT NULL,
    Consulta_linia varchar(10) NOT NULL,
    D_actualitzacio varchar(10) NOT NULL,
    Url varchar(500) NOT NULL,
    Tipus_1 set(‘lingüístic’,’jurídic’) NOT NULL,
    Tipus_2 varchar(1000) NOT NULL,
    Tipus_3 varchar(1000) NOT NULL,
    P_clau varchar(1000) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=132 ;


    — Índices para tablas volcadas


    — Indices de la tabla values

    ALTER TABLE values
    ADD PRIMARY KEY (Id), ADD UNIQUE KEY id (Id);


    — AUTO_INCREMENT de las tablas volcadas


    — AUTO_INCREMENT de la tabla values

    ALTER TABLE values
    MODIFY Id int(6) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=132;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

    #7462

    Sunil Urs
    Keymaster

    Andres,

    At the pie graphic you will see the labels have encoding issues, but they are there, could this be the reason for canvas not showing labels, my data is in utf-8 and words have accents…

    CanvasJS doesn’t have any issue rendering Accented Characters. Problem is that JSON that you are generating doesn’t have label values set – labels are null in case of 2nd, 3rd and 4th chart’s JSON data.

    Given that JSON data of first chart has labels set properly, you can compare that code with that of 2nd chart’s JSON generating code (on the server side).


    Sunil Urs

    #7463

    andres.chandia
    Participant

    I have compare them, and the only difference I find is in the query file not in the js one…
    file any_publicacio.js (the one that correctly shows the labels)

    $(document).ready(function () {
                $.getJSON("any_publicacio.php", function (result) {
                    var chart = new CanvasJS.Chart("chartContainer1", {
                        data: [
                            {
                                dataPoints: result
                            }
                        ]
                    });
                    chart.render();
                });
            });

    File tipus_recursos.js this one does not shows the labels

    $(document).ready(function () {
                $.getJSON("tipus_recursos.php", function (result) {
                    var chart = new CanvasJS.Chart("chartContainer2", {
                        data: [
                            {
                                    type: "pie",
                                dataPoints: result,
                                    indexLabel: "#percent"
                            }
                        ]
                    });
                    chart.render();
                });
            });

    query of the first one, the one that works, file any_publicacio.php

    $data_points = array();
        $result = mysqli_query($con, "SELECT <code>Any_publicacio</code>, COUNT(*) AS 'Publicacions' FROM <code>values</code> GROUP BY <code>Any_publicacio</code> DESC");
        while($row = mysqli_fetch_array($result))
        {
            $point = array("label" => $row['Any_publicacio'] , "y" => $row['Publicacions']);
            array_push($data_points, $point);
        }
        echo json_encode($data_points, JSON_NUMERIC_CHECK);

    query of the second one, the one that does not shows de labels: tipus_recursos.php

    $data_points = array();
        $result = mysqli_query($con, "SELECT <code>Tipus_1</code> AS Tipus, COUNT(*) AS 'Recursos' FROM <code>values</code> GROUP BY <code>Tipus_1</code> DESC");
        while($row = mysqli_fetch_array($result))
        {
            $point = array("label" => $row['Tipus_1'] , "y" => $row['Recursos']);
            array_push($data_points, $point);
        }
        echo json_encode($data_points, JSON_NUMERIC_CHECK);

    The difference in the query is that in the second one I have to put “AS Tipus” otherwise graphic does not renders

    #7473

    andres.chandia
    Participant

    Hi there, any clue yet?

    #7474

    Sunil Urs
    Keymaster

    Andres,

    Issue might be one of the two

    1. $row[‘Tipus_1’] values are null – can you confirm this by logging (echoing) those values??
    2. json_encode is not encoding. What is the type of data??

    In case json_encode has some issue encoding the data, try creating the entire json data using echo statements and string concatination. If you can get those values to show up in JSON data chart will automatically render them.


    Sunil Urs

    #7475

    andres.chandia
    Participant

    Ok, at this file: http://parles.upf.edu/llocs/adljc/grafics/graf_tipus_recursos.php I have put this query:
    SELECTTipus_1AS 'Tipus', COUNT(*) AS 'Recursos' FROMvaluesGROUP BYTipus_1DESC

    You can see the resulting table under the graphic.

    The complete code for rendering the table is this one:

    <?php
    include ("../admin/dbconnect.php");
    $query= "SELECT <code>Tipus_1</code> AS 'Tipus', COUNT(*) AS 'Recursos' FROM <code>values</code> GROUP BY <code>Tipus_1</code> DESC";
    $result = mysql_query($query);
    if (!$result) {
        die("Query to show fields from table failed");
    }
    
    $fields_num = mysql_num_fields($result);
    
    echo "<table class='sortable'><tr>";
    
    // printing table headers
    for($i=0; $i<$fields_num; $i++)
    {
        $field = mysql_fetch_field($result);
        echo "<td align=center>".strtr(" {$field->name} ", $transname)."</td>";
    }
    echo "</tr>\n";
    // printing table rows
    
    while($row = mysql_fetch_row($result))
    {
        echo "<tr onMouseOver=this.bgColor='#e0e0e7'; onMouseOut=this.bgColor='white';>";
    
        // $row is array... foreach( .. ) puts every element
        // of $row to $cell variable
        foreach($row as $cell)
            echo "<td align=center>$cell</td>";
        echo "</tr>\n";
    }
    mysql_free_result($result);
    ?>

    2. Type of data: Tipus_1 set(‘lingüístic’,’jurídic’) NOT NULL,

    #7476

    andres.chandia
    Participant

    In case json_encode has some issue encoding the data, try creating the entire json data using echo statements and string concatination. If you can get those values to show up in JSON data chart will automatically render them.

    Sorry but that sounds like chinese to me….

    #7477

    Sunil Urs
    Keymaster

    Andres,

    Given that echo is working fine, please try creating this JSON using echo statements and it should work.


    Sunil Urs

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

You must be logged in to reply to this topic.