How populate chart.js with sql data?

Question

I'm using chart.js to generate charts on my page. However I want these charts to be populated by my SQL database. I'm able to get my data out of my database, but I won't draw the chart

I got a canvas on my main page called "OmzetChart" , this is where the chart should come.

<script>
    $.ajax({
        type: 'POST',
        url: 'templates/getdata.php',
        success: function (data) {
            lineChartData = data;
            //alert(JSON.stringify(data));

            var ctx = document.getElementById("OmzetChart").getContext("2d");
            var myLineChart = new Chart(ctx, {
                type: 'line',
                data: lineChartData

            });
       }
    });

</script>

The page of GetData.php results in the following (This is what I need, just want it into my chart):

[{"dag":"23","0":"23","uur":"13","1":"13","SomOmzet":"23.00","2":"23.00"},{"dag":"23","0":"23","uur":"18","1":"18","SomOmzet":"2.50","2":"2.50"}]

Getdata.php: 
<?php
include ("../PDO.php");


$conn = DatabasePDO::getInstance();
$sql = "SELECT DATEPART(DD, receiptdatetime) as dag ,DATEPART(hh, receiptdatetime) as uur, ISNULL(abs(cast(sum(NetAmount) as decimal (10,2))),0) as SomOmzet FROM ReceiptLine a , Receipt b, ReceiptLineDetail c 
where a.LineType = 200 and a.receiptID = b.receiptid and a.receiptlineID = c.receiptlineID
group by DATEPART(DD, receiptdatetime), DATEPART(hh, receiptdatetime)";
$st = $conn->prepare($sql);
$st->execute();
$list = array();

while ( $row = $st->fetch() ) {
    $list[] = $row;

}
$conn = null;

echo json_encode( $list );

?>

Well, you have the ajax tag in there so I assume you know about ajax. Why is there no ajax request to get data in your code? — Ozan
– Ozan, Commented Aug 7, 2016 at 16:19
@ozan , I tried many many times, however I never get it to work. That's why I ask here — Steven
– Steven, Commented Aug 7, 2016 at 16:21
Have you tried making an ajax call? If so, please share your ajax code. If not you didn't, that's where you should start. — Ozan
– Ozan, Commented Aug 7, 2016 at 16:29
@ozan , I've updated my post. This code works until the drawing. It just gives me a blank canvas — Steven
– Steven, Commented Aug 7, 2016 at 20:53

Ozan · Accepted Answer · 2016-08-07 21:15:27Z

3

json_encode() produces a JSON string. You need to parse this with JSON.parse() before you can use it.

$.ajax({
    type: 'POST',
    url: 'templates/getdata.php',
    success: function (data) {
        lineChartData = JSON.parse(data); //parse the data into JSON

        var ctx = document.getElementById("OmzetChart").getContext("2d");
        var myLineChart = new Chart(ctx, {
            type: 'line',
            data: lineChartData
        });
    }
});

Also, using $.ajax() method's dataType parameter, you can leave this parsing to jQuery.

$.ajax({
    type: 'POST',
    url: 'templates/getdata.php',
    dataType: 'json', //tell jQuery to parse received data as JSON before passing it onto successCallback
    success: function (data) {
        var ctx = document.getElementById("OmzetChart").getContext("2d");
        var myLineChart = new Chart(ctx, {
            type: 'line',
            data: data //jQuery will parse this since dataType is set to json
        });
    }
});

answered Aug 7, 2016 at 21:15

Ozan

3,7492 gold badges21 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ozan Over a year ago

@Steve, your data is not in the correct format. Take a look here. data should be an object containing the necessary information.

Collectives™ on Stack Overflow

How populate chart.js with sql data?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related