使用mysql数据创建条形图

Question

I want create bar using mysql data which is returned in JSON Format.

[
  {
    "Score": "84",
    "Trainer": "Jamshaid",
    "Subject Name": "Java"
  },
  {
    "Score": "50",
    "Trainer": "Zaid",
    "Subject Name": "Android"
  },
  {
    "Score": "40",
    "Trainer": "Sajjad",
    "Subject Name": "iOS"
  },
  {
    "Score": "50",
    "Trainer": "Jamshaid",
    "Subject Name": "iOS"
  }
]

I want to create like this chart

But problem is that this gets formatted from Table. But I have data in JSON as shown above.

Here is the link I am following. Can we convert the JSON Data in a Format so that it can populate chart based on the Data as shown

https://blueflame-software.com/bar-chart-with-data-from-mysql-using-highcharts/

Answer 1

You can process the JSON in the Javascript to build the data required for the chart as shown below:

var jsonFromServer = [
  {
    "Score": "84",
    "Trainer": "Jamshaid",
    "Subject Name": "Java"
  },
  {
    "Score": "50",
    "Trainer": "Zaid",
    "Subject Name": "Android"
  },
  {
    "Score": "40",
    "Trainer": "Sajjad",
    "Subject Name": "iOS"
  },
  {
    "Score": "50",
    "Trainer": "Jamshaid",
    "Subject Name": "iOS"
  }
];

The above is data from server captured in a variable

The below code helps you to extract the Y axis Values (i.e the unique subject names) from the JSON response:

function getSubjects(){
    var subjectsMap = {};
  $.each(jsonFromServer, function(index, value){
    if(!subjectsMap[value['Subject Name']]){
        subjectsMap[value['Subject Name']] = 1;
    }
  });
  return $.map(subjectsMap, function(index, val){ return val; });
}

Then we need to extract the Scores for each trainer in different subject which should be of form: [{name: "Trainer", data: []}] where data is an array of scores in subject whose order should be same as the order of subjects appearing in the Y axis data. Which can be achieved using the below code:

function getTrainers(){
    var trainersMap = {};
  $.each(jsonFromServer, function(index, value){
    if(!trainersMap[value['Trainer']]){
        trainersMap[value['Trainer']] = 1;
    }
  });
  return $.map(trainersMap, function(index, val){ return val; });
}
function getMarks(){
  var subjects = getSubjects();
  var trainers= getTrainers();
  var marks = {};
  //initialize the trainers scores in all subjects to 0
   $.each(trainers, function(index, trainer){
    if ( !marks[trainer]){
        marks[trainer] = {};
    }
    $.each(subjects, function(idx2, sub){
        marks[trainer][sub] = 0;
    });
  });

  //now populate with the actual values obtained from server
  $.each(subjects, function(idx2, sub){
    $.each(jsonFromServer, function(index, value){
        var trainer = value['Trainer'];
        var subName = value['Subject Name'];
      var score = value['Score'];
      if ( sub == subName){
            marks[trainer][sub] = score;  
      }
    });

  });

  //now format the marks object into the format required for highcharts
  //i.e an array of object with properties [{name: "", data:[]}]
  return $.map(marks, function(val, index){ 
    var scoreArray = [];
    $.each(val, function(index, score){
        scoreArray.push(parseInt(score));
    });
    return {"name": index, "data": scoreArray}; 
  });

}

The working code can be found in the fiddle here.