Home › Forums › Chart Support › Issues retrievng data from MySql

Issues retrievng data from MySql

Viewing 15 posts - 1 through 15 (of 28 total)
1 2
  • October 30, 2014 at 6:50 pm #7421

    andres.chandia

    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

    October 31, 2014 at 3:42 pm #7446

    Anjali

    Andres,

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

    Chart with Custom Labels on X Axis

    __
    Anjali

    October 31, 2014 at 5:38 pm #7449

    andres.chandia

    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.

    October 31, 2014 at 6:20 pm #7450

    Anjali

    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

    October 31, 2014 at 7:50 pm #7451

    andres.chandia

    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();
        });
    November 3, 2014 at 12:39 pm #7455

    Anjali

    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 the documentation page.

    Wrap axisX labels in chart

    __
    Anjali

    November 3, 2014 at 3:14 pm #7456

    andres.chandia

    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.

    November 3, 2014 at 4:57 pm #7458

    andres.chandia

    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 */;

    November 3, 2014 at 6:05 pm #7462

    Sunil Urs

    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

    November 3, 2014 at 6:31 pm #7463

    andres.chandia

    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

    November 5, 2014 at 4:35 pm #7473

    andres.chandia

    Hi there, any clue yet?

    November 5, 2014 at 5:26 pm #7474

    Sunil Urs

    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

    November 5, 2014 at 7:34 pm #7475

    andres.chandia

    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,

    November 5, 2014 at 7:38 pm #7476

    andres.chandia

    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….

    November 5, 2014 at 7:45 pm #7477

    Sunil Urs

    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)
1 2

You must be logged in to reply to this topic.