Dynamic dataset from MySQL query with Chart.js

Question

I am trying to create a dynamic dataset for my Chart.js project but I am having trouble, here is an example of the dataset pulled from getpromorevenuechart.php:

[{"mmyy":"2019-12","promocode":"promo1","amount":"2776"},{"mmyy":"2020-01","promocode":"promo1","amount":"1245"},{"mmyy":"2020-01","promocode":"promo2","amount":"179"}]

Here is the code I am currently using:

    function getPromoRChartData() {
    var city = document.getElementById("cityselect").value;
$.ajax({
        type: 'GET',
        url: 'getpromorevenuechart.php',
        dataType: 'json',
        data: {  city:city, },
        success: function(response) {
          //console.log (response);

          function collate(d) {
            return d.reduce(function(prev, cur, index) {
                var ret = {};
                for (var prop in cur) {
                    if (index === 0) {
                        ret[prop] = [];
                    } else {
                        ret[prop] = prev[prop];
                    }
                    ret[prop].push(cur[prop]);
                }
                return ret;
            }, {});
        }

          var reduced = collate(response);

var ctx = document.getElementById('promorChart').getContext('2d');

var chartColors = window.chartColors;
var color = Chart.helpers.color;

var fleetmChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: reduced.mmyy,
        datasets: [{
        label: reduced.promocode,
        data: reduced.amount,
    },
    options: {
        scales: {
            xAxes: [{
              stacked: false
            }],
            yAxes: [{
              stacked: false,
                ticks: {
                    // Include a dollar sign in the ticks
                    callback: function(value, index, values) {
                        return '$' + value;
                    }
                }
            }]
        },
        tooltips: {
            callbacks: {
                label: function(tooltipItems, data) {
                    return "$" + tooltipItems.yLabel.toString();
                }
            }
        },
        responsive: true,
        elements: {
        }
    }
});

But that code isn't displaying the way I want. The way I want this to look is like the following:

data: {
        labels: ["2019-12", "2020-01"],
        datasets: [{
            label: 'promo 1',
            data: [2776, 1245]
        },
        {
            label: 'promo 2',
            data: [0, 179]
        }
        ]
    },

What it seems I need to do is create a for each type thing and I would also need to introduce a 0 for mm/yy when that promo isn't used. I am very unsure how to do all this. Can someone give me some direction and assist me in how to make this happen?

Edit:

Trying to incorporate uminder's code below:

    function getPromoRChartData() {
    var city = document.getElementById("cityselect").value;
$.ajax({
        type: 'GET',
        url: 'getpromorevenuechart.php',
        dataType: 'json',
        data: {  city:city, },
        success: function(response) {
const labels = Array.from(new Set(response.map(c => c.mmyy))).sort();
const promocodes = Array.from(new Set(response.map(c => c.promocode))).sort();
const datasets = promocodes.map(pc => ({ label: pc, data: []}));
labels.forEach(l => {    
    for (let pc of promocodes) {
      let city = response.find(c => c.mmyy == l && c.promocode == pc);
        datasets.find(ds => ds.label == pc).data.push(city ? city.amount : 0);
    }
});

var dynlabels = JSON.stringify(labels);
var dyndatasets = JSON.stringify(datasets, undefined, "  ");

console.log (dynlabels);
console.log (dyndatasets);

var ctx = document.getElementById('promorChart').getContext('2d');

var chartColors = window.chartColors;
var color = Chart.helpers.color;

var promorChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: [dynlabels],
        datasets: [{
            dyndatasets
        }
        ]
    },

    options: {
        scales: {
            xAxes: [{
              stacked: false
            }],
            yAxes: [{
              stacked: false,
                ticks: {
                    // Include a dollar sign in the ticks
                    callback: function(value, index, values) {
                        return '$' + value;
                    }
                }
            }]
        },
        tooltips: {
            callbacks: {
                label: function(tooltipItems, data) {
                    return "$" + tooltipItems.yLabel.toString();
                }
            }
        },
        responsive: true,
        elements: {
        }
    }
});

does not render the graph correctly at all, here is a screen shot of the graph rendered (using the above input data):

and here is the console output:

    ["2019-12","2020-01"]
    success — Script Element 2:1382[
      {
        "label": "promo1",
        "data": [
          "2776",
          "1245"
        ]

  },
  {
    "label": "promo2",
    "data": [
      0,
      "179"
    ]
  }
]

If I remove the [{}] around dyndatasets I get the error:

TypeError: Attempted to assign to readonly property.

and if I remove the brackets from dynlabels this happens:

Oddly enough... if I copy directly from the console and past in the fields it works, so I don't know what gives? Is it like a formatting error?

Answer 1

Here's a code sample that illustrates how to convert the response into the data (labels and datasets) required by chart.js.

const response = [ 
   { 
      "mmyy":"2019-12",
      "promocode":"promo1",
      "amount":"2776"
   },
   { 
      "mmyy":"2020-01",
      "promocode":"promo1",
      "amount":"1245"
   },
   { 
      "mmyy":"2020-01",
      "promocode":"promo2",
      "amount":"179"
   }
];

const labels = Array.from(new Set(response.map(c => c.mmyy))).sort();
const promocodes = Array.from(new Set(response.map(c => c.promocode))).sort();
const datasets = promocodes.map(pc => ({ label: pc, data: []}));
labels.forEach(l => {    
    for (let pc of promocodes) {
    	let city = response.find(c => c.mmyy == l && c.promocode == pc);
        datasets.find(ds => ds.label == pc).data.push(city ? Number(city.amount) : 0);
    }
});

console.log("labels: " + JSON.stringify(labels));
console.log("datasets: " + JSON.stringify(datasets, undefined, "  "));

Please have a look at the following JSFiddle