Home › Forums › Chart Support › SQLServer + PHP + Canvasjs

SQLServer + PHP + Canvasjs

Viewing 12 posts - 1 through 12 (of 12 total)
  • May 24, 2019 at 5:53 am #25280

    B_Juarez

    Dear all,

    I’m trying to show information in a “doughnut” chart using PHP and SQL Server

    If I build a manual array like this, it works:
    $dataPoints = array(
    array(“y” => 4, “label” => “Bancario y Financiero”),
    array(“y” => 4, “label” => “Comercio Exterior”),
    array(“y” => 8, “label” => “Competencia Económica”),
    array(“y” => 28, “label” => “Corporativo”)
    );

    Output:
    dataPoints: [{“y”:4,”label”:”Bancario y Financiero”},{“y”:4,”label”:”Comercio Exterior”}, {“y”:8,”label”:”Competencia Econ\u00f3mica”},{“y”:28,”label”:”Corporativo”}] }

    With next code:
    $queryG = “SELECT count(A.CveAsunto) as y, TL.Nombre as label
    FROM Table_1 TL INNER JOIN Table_2 A ON TL.CveK1=A.CveK2
    WHERE YEAR(A.FechaApertura)=2019 and A.CveAsuntoEstatus=1 GROUP BY TL.Nombre”;
    $dataG = sqlsrv_query($conn, $queryG);
    $dataPoints = array();
    while( $row = sqlsrv_fetch_array($dataG, SQLSRV_FETCH_ASSOC) ) {
    array_push($dataPoints, $row);
    }
    I receive next output:
    dataPoints: }
    ]
    error message: Uncaught SyntaxError: Unexpected token }

    Do you have any comment to help me please.

    Regards

    May 24, 2019 at 6:34 pm #25290

    Manoj Mohan

    @b_juarez,

    Please take a look at this Gallery Example for rendering chart with data from the database. You can also download PHP sample from our download page.

    If this doesn’t help you resolve the issue you are facing, kindly share sample project along with sample database over Google Drive / One Drive so that we can run it locally at our end, understand the scenario better and help you resolve it.

    —-
    Manoj Mohan
    Team CanvasJS

    May 24, 2019 at 7:21 pm #25291

    B_Juarez

    @manoj-mohan

    Thanks a lot for your help, I used MySQL and my chart worked fine, but when I changed to SQLSrv, didn’t work. The connection to DB is working and information is the same than MySQL, the code is next:

    Techinal Requirements:
    – XAMPP for Windows 5.6.39
    – PHP Version 5.6.39
    php.ini file (I downloaded the SQLSrv drivers to establish the connection between my develop environment with my DB in another sever)
    extension=php_pdo_sqlsrv_56_ts.dll
    extension=php_sqlsrv_56_ts.dll

    Thanks again and let me know if you need more detail please.

    CODE:

    <?php
$serverName = "192.2.1.21, 1433"; //serverName\instanceName, portNumber (default is 1433)
$connectionInfo = array( "Database"=>"Legal", "UID"=>"sa", "PWD"=>"T07Pass");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$queryG = "SELECT count(A.CveAsunto) as y, TL.Nombre as label 
		   FROM GeniusLegal.dbo.TipoLey TL INNER JOIN GeniusLegal.dbo.Asunto A ON  TL.CveTipoLey=A.CveTipoLey 
		   WHERE YEAR(A.FechaApertura)=2019 and A.CveAsuntoEstatus=1 GROUP BY TL.Nombre";
$dataG = sqlsrv_query($conn, $queryG);
$dataPoints = array();
while( $row = sqlsrv_fetch_array($dataG, SQLSRV_FETCH_ASSOC) ) {
	  array_push($dataPoints, $row);
}
?>
<!DOCTYPE HTML>
<html>
<head>
	<meta charset="UTF-8">
	<title> Dashboard - © 2019 Powered by Jauregui y Del Valle, S.C. </title>
	<meta name="description" content="JDV">
	<meta name="author" content="JDV">
	<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="stylesheet" type="text/css" href="source/bootstrap-3.3.6-dist/css/bootstrap.css">
	<link rel="stylesheet" type="text/css" href="source/font-awesome-4.5.0/css/font-awesome.css">
	<link rel="stylesheet" type="text/css" href="style/slider.css">
	<link rel="stylesheet" type="text/css" href="style/mystyle.css">
<script type="text/javascript">
window.onload = function () {
        var chart = new CanvasJS.Chart("chartContainer", {
            title: {
                text: "Asuntos \"Activos\" por tipo"
            },
            animationEnabled: true,
            legend: {
                fontSize: 12,
                fontFamily: "Helvetica"
            },
            theme: "light2",
            data: [
            {
                type: "doughnut",
                indexLabelFontFamily: "Garamond",
                indexLabelFontSize: 13,
                indexLabel: "{label}: {y}",
                startAngle: -20,
                showInLegend: false,
				dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
            }
            ]
        });
        chart.render();
}
</script>
</head>
<body>
<div id="chartContainer" style="width: 50%; height: 300px;display: inline-block;"></div>
<script src="../canvasjs-2.2/canvasjs.min.js"></script>
</body>
</html>
<?php 
	sqlsrv_close($conn);
?>
    May 27, 2019 at 4:54 pm #25311

    Manoj Mohan

    @b_juarez,

    We are looking into your query and will get back to you at the earliest.

    —–
    Manoj Mohan
    Team CanvasJS

    May 28, 2019 at 7:05 pm #25323

    B_Juarez

    Thank you Manoj,

    I continue researchig.

    Regards

    June 7, 2019 at 8:34 pm #25444

    Manoj Mohan

    @b_juarez,

    It seems to be working fine with SQL Server(v14.0.1000.169) and PHP versions (v7.0+). Kindly check with the PHP v7.0+ as there seems to be an issue with SQL Driver and PHP v5.6. Once connected kindly check whether the data you are receiving from the database is being parsed to the format accepted by CanvasJS ({label: “Apple”, y: 6}) or not.

    —–
    Manoj Mohan
    Team CanvasJS

    June 7, 2019 at 9:51 pm #25446

    B_Juarez

    @manoj-mohan

    Thanks a lot for your tip. Before PHP 5.6 I was programming in PHP 7.3, but my Operative System is at 32 bits and I didn’t find the SQL controller for this version (PHP 7 + OS 32 bits), let me try at 64 bits OS and reload the SQL controllers. Let you know the results.

    Best Regards
    Keep in touch

    June 11, 2019 at 6:27 am #25478

    B_Juarez

    Hello @manoj-mohan

    I reinstalled CanvasJS in a 64 bits operative system and PHP 7.3. I downloaded the libraries for SQL Server ans installed the controllers, but I continue with the problem.

    If I generate the array manually, I don’t have problems, but when I get the information from my SQL Server Database its imposible display the chart, I show the informarion with “echo” or “print_r”, but the chart never appears.

    Regards.

    June 11, 2019 at 8:37 pm #25486

    Manoj Mohan

    @b_juarez,

    It seems to be working fine. Please take a look at this working sample project along with the database.

    Also, please take a look at this documentation for more information.

    —-
    Manoj Mohan
    Team CanvasJS

    June 13, 2019 at 12:01 am #25512

    B_Juarez

    Hello @manoj-mohan

    Thanks a lot for your help, I found the problem, If I get only numbers from database the chart works fine

    
$dataPoints = array(
        array("y" => 4, "label" => 56),
        array("y" => 4, "label" => 68),
        array("y" => 8, "label" => 12),
        array("y" => 28, "label" => 1)
);

But, when I get a string, the chart doesn't work.

$dataPoints = array(
        array("y" => 4, "label" => "Bancario y Financiero"),
        array("y" => 4, "label" => "Comercio Exterior"),
        array("y" => 8, "label" => "Competencia Económica"),
        array("y" => 28, "label" => "Corporativo")
);

    Do you have any idea?

    Best regards

    June 13, 2019 at 5:55 am #25513

    B_Juarez

    Hello @manoj-mohan

    I really apreciate your help, at last I found the source of the problem, In Spanish we use the ortigrafic accent and I’m working with word “Económica”, when I get the information from DB the word appears like this “Econ�mica” and the strange character is the problem. I need find a solution to remplace this character since the DB query because I can’t change the information inside the table.

    Best regards

    June 13, 2019 at 5:33 pm #25515

    Manoj Mohan

    @b_juarez,

    Glad that you were able to figure out the issue. In order to get UTF-8 characters from the database, you can set CharacterSet to UTF-8 in the connection options as shown in this documentation page.

    —–
    Manoj Mohan
    Team CanvasJS

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.