Chart.js - Getting data from database using mysql and php

Question

I'm trying to convert the static data to using database results. I'll be using MySQL and PHP.

Example Code:

var randomScalingFactor = function(){ return Math.round(Math.random()*100)};
var lineChartData = {
    labels : ["January","February","March","April","May","June","July"],
    datasets : [
        {
            label: "My First dataset",
            fillColor : "rgba(220,220,220,0.2)",
            strokeColor : "rgba(220,220,220,1)",
            pointColor : "rgba(220,220,220,1)",
            pointStrokeColor : "#fff",
            pointHighlightFill : "#fff",
            pointHighlightStroke : "rgba(220,220,220,1)",
            data : [randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor()]
        },
        {
            label: "My Second dataset",
            fillColor : "rgba(151,187,205,0.2)",
            strokeColor : "rgba(151,187,205,1)",
            pointColor : "rgba(151,187,205,1)",
            pointStrokeColor : "#fff",
            pointHighlightFill : "#fff",
            pointHighlightStroke : "rgba(151,187,205,1)",
            data : [randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor(),randomScalingFactor()]
        }
    ]
    }
window.onload = function(){
    var ctx = document.getElementById("canvas").getContext("2d");
    window.myLine = new Chart(ctx).Line(lineChartData, {
        responsive: true
    });
}

Below is my php/msql:

$result = mysql_query("SELECT COUNT(*) FROM customer WHERE month='january'") or die(mysql_error());
$row1 = mysql_fetch_array( $result );

$result = mysql_query("SELECT COUNT(*) FROM customer WHERE month='february'") or die(mysql_error());
$row2 = mysql_fetch_array( $result );

$result = mysql_query("SELECT COUNT(*) FROM customer WHERE month='march'") or die(mysql_error());
$row3 = mysql_fetch_array( $result );

How could I use those count() from my MySQL query and implement it to datasets on chartjs? I would also like the labels to be generated from my MySQL query too. Should I loop the datasets inside the jQuery code?

This is the plugin that I'm using : http://www.chartjs.org/docs/#line-chart-introduction

score 6 · Answer 1 · answered Jun 19 '15 at 11:27

First get your data into suitable data structures using PHP

$months = array("january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december");
$monthvalues = array();
foreach ($months as $month) {
    $monthvalues[$month] = 0;
}

$result = mysql_query("SELECT month, count(*) FROM customer group by month") or die(mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $monthvalues[$row[0]] = (int)$row[1];
}

Below that, just plug in those data structures into your Javascript

var randomScalingFactor = function(){ return Math.round(Math.random()*100)};
var lineChartData = {
    labels : <?=json_encode($months);?>,
    datasets : [
        {
            label: "My First dataset",
            fillColor : "rgba(220,220,220,0.2)",
            strokeColor : "rgba(220,220,220,1)",
            pointColor : "rgba(220,220,220,1)",
            pointStrokeColor : "#fff",
            pointHighlightFill : "#fff",
            pointHighlightStroke : "rgba(220,220,220,1)",
            data : <?=json_encode(array_values($monthvalues));?>
        }
    ]
}

assuming the the window.onload and the HTML for the canvas element are in their proper places.

You might have to check the syntax :-) I don't know PHP and just thought I'd give it a whirl. — potatopeelings, Jun 19 '15 at 11:28
Can you help me please, i have a problem with chart system please check my question for me if you have a solution please tell me. Here is my question link : https://stackoverflow.com/questions/49066512/how-many-people-are-registering-in-the-day-of-the-week — AlwaysStudent, Mar 02 '18 at 11:02
the problem here if you have many datasets, it too much time and not reuseable — Shin Sang Ki, Aug 23 '18 at 03:48

score 5 · Accepted Answer · answered Jun 19 '15 at 13:56

Please place your php code into another file called api.php and use $.ajax to get these data with JSON format. To convert data into JSON format data you should use json_encode() php function.

I have set sample example you can see here.

Please refer below code example:

api.php

$arrLabels = array("January","February","March","April","May","June","July");
$arrDatasets = array('label' => "My First dataset",'fillColor' => "rgba(220,220,220,0.2)", 'strokeColor' => "rgba(220,220,220,1)", 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#fff", 'pointHighlightFill' => "#fff", 'pointHighlightStroke' => "rgba(220,220,220,1)", 'data' => array('28', '48', '40', '19', '86', '27', '90'));

$arrReturn = array(array('labels' => $arrLabels, 'datasets' => $arrDatasets));

print (json_encode($arrReturn));

example.html

$.ajax({
type: 'POST',
url: 'api.php',
success: function (data) {
lineChartData = data;//alert(JSON.stringify(data));
var myLine = new Chart(document.getElementById("canvas").getContext("2d")).Line(lineChartData);

var ctx = document.getElementById("canvas").getContext("2d");
window.myLine = new Chart(ctx).Line(lineChartData, {responsive: true});
} 
});

Please note that you should pass value of randomScalingFactor() at api.php.

Please check and let me know if you require any further help.

I might be a bit late to the party, but [this PHPChartJS library might help](https://github.com/halfpastfouram/PHPChartJS/) — halfpastfour.am, Nov 24 '16 at 09:01
The example in the link is not working. There is no chart displayed, and an error message saying "Uncaught ReferenceError: Chart is not defined" — Supernormal, May 31 '20 at 07:39

score 0 · Answer 3 · edited Sep 16 '19 at 14:02

This is based on the answer above with a couple of changes.

php:

include 'db.php';
$query = "SELECT month, COUNT(*) count FROM customer WHERE month='march' GROUP BY month";

if ($stmt = $conn->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($month, $count);            

    $labels = array();
    $data = array();

    while ($stmt->fetch()) {
        $labels[] = $month;
        $data[] = $count;
    }
        $stmt->close();
}

$datasets = array('label'=>"timer",'data'=> $data);
$data = array('labels'=>$labels, 'datasets'=> array($datasets));

echo json_encode($data);

I had to use JSON.pare() on the passed-in array.

Javascript:

$.ajax({
    type: 'POST',
    url: 'api.php ',
    datatype: 'json',
    success: function (result) {
        var ctx = document.getElementById("chart").getContext("2d");
        var mychart = new Chart(ctx,
        {
            type: 'bar',
            data: JSON.parse(result),
            options: {
                scales: {
                    yAxes: [{
                        ticks: {
                            beginAtZero: true
                        }
                    }]
                }
            }
        })
    }
})

html:

<canvas id="chart" width="200" height="200"></canvas>

Chart.js - Getting data from database using mysql and php

3 Answers3

Linked