How to retrieve data from mysql to chart.js

Question

I want to create a pie chart with chart.js, but it can't display the chart. I spent a day trying to solve this problem, but there was no good result. I hope someone can help me.

My database has different companies, I need to calculate the total sales of each company and display in the pie chart.

I think the problem would be $results_sum = "SELECT SUM(total_of_gp_fee) AS Total FROM gp WHERE cshortcut=$subjectData('cshortcut')";, since there has many sales record for different company and I am not sure if that is correct code.

<?php
include_once("connection.php");
$results_sum = "SELECT cshortcut,SUM(total_of_gp_fee) AS Total FROM gp GROUP BY cshortcut";
$result_sum = mysqli_query($conn, $results_sum) or die("error to fetch data");
if ($result_sum->num_rows > 0) {
// output data of each row
$labels = $data = '';
while($row = $result_sum->fetch_assoc()) {

    //get the company name separated by comma for chart labels
    $labels.= '"' .$row["cshortcut"]. '",';

    //get the total separated by comma for chart data
    $data.= $row["Total"].',';
}
}?>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">

<title>test</title>
<script src="chart/Chart.bundle.js"></script>

</head>
<body>
<div class="container">
    <canvas id="myChart" width="300" height="100"></canvas>
</div>

script part

<script>
    var ctx = document.getElementById("myChart");
    var myChart = new Chart(ctx, {
        type: 'pie',
        data: {
            labels: [<?php echo trim($labels);?>],
        datasets: [{
                label: '# of Votes',
                data: [<?php echo trim($data);?>],
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.2)',
                        'rgba(54, 162, 235, 0.2)',
                        'rgba(255, 206, 86, 0.2)',
                        'rgba(75, 192, 192, 0.2)',
                        'rgba(153, 102, 255, 0.2)',
                        'rgba(255, 159, 64, 0.2)',
                        'rgba(255, 99, 132, 0.2)',
                        'rgba(54, 162, 235, 0.2)',
                        'rgba(255, 206, 86, 0.2)',
                        'rgba(75, 192, 192, 0.2)',
                        'rgba(153, 102, 255, 0.2)',
                        'rgba(255, 159, 64, 0.2)'
                    ],
                    borderColor: [
                        'rgba(255,99,132,1)',
                        'rgba(54, 162, 235, 1)',
                        'rgba(255, 206, 86, 1)',
                        'rgba(75, 192, 192, 1)',
                        'rgba(153, 102, 255, 1)',
                        'rgba(255, 159, 64, 1)',
                        'rgba(255, 99, 132, 0.2)',
                        'rgba(54, 162, 235, 0.2)',
                        'rgba(255, 206, 86, 0.2)',
                        'rgba(75, 192, 192, 0.2)',
                        'rgba(153, 102, 255, 0.2)',
                        'rgba(255, 159, 64, 0.2)'
                    ],
                    borderWidth: 1
                }]
        },
        options: {
        scales: {
            yAxes: [{
                    ticks: {
                        beginAtZero: true
                    }
                }]
        },

        //Add the tooltips
        tooltips: {
                callbacks: {
                    label: function(tooltipItem) {
                        return "€ " + Number(tooltipItem.yLabel);
                    }
                }
        },
    }
        },
     );
</script>

Shivendra Singh · Accepted Answer · 2019-06-09T13:51:04.723

1

1- get the company name and SUM of total_of_gp_fee group by company.

include_once("connection.php");

//get the company name and total_of_gp_fee of that company.
$results_sum = "SELECT cshortcut,SUM(total_of_gp_fee) AS Total FROM gp GROUP BY cshortcut";
$result_sum = mysqli_query($conn, $results_sum) or die("error to fetch data");
if ($result_sum->num_rows > 0) {
    // output data of each row
    $labels = $data = '';
    while($row = $result_sum->fetch_assoc()) {

        //get the company name separated by comma for chart labels
        $labels.= '"' .$row["cshortcut"]. '",';

        //get the total separated by comma for chart data
        $data.= $row["Total"].',';
    }
}

2- Update the value of labels and data in chart.

labels: [<?php echo trim($labels);?>],
            datasets: [{
                    label: '# of Votes',
                    data: [<?php echo trim($data);?>],

3- Add the tooltips for bar chart.

options: {
            scales: {
                yAxes: [{
                        ticks: {
                            beginAtZero: true
                        }
                    }]
            },

            //Add the tooltips
            tooltips: {
                    callbacks: {
                        label: function(tooltipItem) {
                            return "€" + Number(tooltipItem.yLabel);
                        }
                    }
            },
        }

4- Add tooltips for pie chart.

tooltips: {
                callbacks: {
                    label: function(tooltipItem, data) {
                        var allData = data.datasets[tooltipItem.datasetIndex].data;
                        var tooltipData = allData[tooltipItem.index];
                        var total = 0;
                        for (var i in allData) {
                            total += allData[i];
                        }
                        var tooltipPercentage = Math.round((tooltipData / total) * 100);
                        return "€" + ': ' + tooltipData + ' (' + tooltipPercentage + '%)';
                    }
                }
            },

edited Jun 09 '19 at 13:51

answered Jun 06 '19 at 09:56

Shivendra Singh

2,986
1
11
11

hi, thanks again, but how to display EURO Symbol in front of the number? I have try to do like this data: [], but it doesn't work. – henrik Jun 06 '19 at 13:48
1

In the bar chart remove the € symbol from data otherwise it will not work. Add tooltips within option to make custom labels. I added in code please have a look. – Shivendra Singh Jun 06 '19 at 15:03
1

I tried it's working for me. Can you please update your complete java script code. I will check. – Shivendra Singh Jun 07 '19 at 02:49
1

You are using bar chart or pie chart.? Update your complete jQuery code – Shivendra Singh Jun 09 '19 at 09:53
I updated complete javascript code, I have try bar chart which success to show € symbol with sales number now, but it could only show € symbol without sales number when turn to pie chart, is it possible to show percentage as well? thanks – henrik Jun 09 '19 at 13:26
1

Hi, update the code to show percentage as well for pie chart. Please check – Shivendra Singh Jun 09 '19 at 13:52
Thanks, it works. Please help again if you have time https://stackoverflow.com/questions/56539523/how-can-i-update-all-row-from-database-and-show-updated-data-without-reflesh/56539804#56539804 – henrik Jun 11 '19 at 08:50
1

If it's working. Can you please vote up? thank you.. :) – Shivendra Singh Jun 11 '19 at 14:16
certainly, you help me a lot, I thought I have already voted you, how about now? actually I don't how to vote... – henrik Jun 11 '19 at 15:43
Yes, now it's okay. Thank you so much. :) – Shivendra Singh Jun 11 '19 at 15:49
See the link for vote. https://stackoverflow.com/help/privileges/vote-up – Shivendra Singh Jun 11 '19 at 15:54

score 0 · Answer 2 · answered Jun 06 '19 at 09:20

I think cshortcut holds the name of the comany, so you can do it all with one query:

$results_sum = "SELECT cshortcut,SUM(total_of_gp_fee) AS Total FROM gp GROUP BY cshortcut";

And you will get an array with to elements (cshortcut and Total) for each element in the array

Anyway, you have an error in your JS, you are using a name that doesn 't exist in your data (total_of_gp_fee) because you are using an alias (Total), you should change:

        labels: [<?php while ($b = mysqli_fetch_array($result_cut)) { echo '"' . $b['cshortcut'] . '",';}?>],
        datasets: [{
                label: '# of Votes',
                data: [<?php while ($p = mysqli_fetch_array($sum)) { echo '"' . $p['Total'] . '",';}?>],

data: [], should change to data: [], Thanks – henrik Jun 06 '19 at 09:49 — henrik, Jun 06 '19 at 09:49

How to retrieve data from mysql to chart.js

2 Answers2

Linked