Connect Handsontable To Mysql Server

I am trying to use the Handsontable javascript library as a 'real time' CRUD interface to MySQL server. I have created a basic script to load up an instance of Handsontable in a br

Solution 1:

Based on your comment, I assume you already have you data in JSON format available on a URL as well as a URL ready to get the data (same format) to upload your database

---

For what you need to do, you've got pretty much everything explain it this Handsontable documentation example.

You will load your data one time, and save your data in the afterChange event.

Let's take your Handsontable definition and add to it the "realtime" saving function like the example in the documentation :

var container = document.getElementById('example');
var hot = newHandsontable(container, {
   minSpareRows: 1,
   rowHeaders: true,
   colHeaders: true,
   contextMenu: trueafterChange: function (change, source) {
       ajax('yourJsonPath/save.json', 'GET', JSON.stringify({data: hot.getData()}), function (res) {
         var response = JSON.parse(res.response);

         if (response.result === 'ok') {
             console.log("Data saved");
         }
         else {
            console.log("Saving error");
         }
    });
  }
});

Below that, let's load the data one time when you open your page :

ajax('yourJsonPath/load.json', 'GET', '', function(res) {
  var data = JSON.parse(res.response);

  if (data.result === 'ok') {
    hot.loadData(data.data);
    console.log("Data Loaded");
  }
  else {
    console.log("Loading error");
  }

});

The key handsontable functions which allow you to load and save your data present in your table are :

hot.loadData(data) // To put data into your table
hot.getData() // To extract the current data present in your table

If you use jQuery (and I do have a personal preference to Post and Get with it), the equivalent of the ajax function would be :

// For Saving
jQuery.ajax({
  type: "POST",
  'url':'yourJsonPath/save.json',
  data: JSON.stringify(hot.getDate()),
  headers: {
    'Accept': 'application/json',
    'Content-Type': 'application/json'
  },
  'success': function () {
    console.log("Data Saved");
  },
  'error': function () {
    console.log("Saving error");
  }
});

// For Loading
jQuery.ajax({
  type: "GET",
  headers: {
    'Accept': 'application/json',
    'Content-Type': 'application/json'
  },
  'url':'yourJsonPath/load.json',
  'success': function (res) {
    hot.loadData(res.data);
  },
  'error': function () {
    console.log("Loading error");
  }
});

Again, this assume that you do have the back-end (your PHP code in your case) ready to put and pull data from the interface, but as said in the comment this is completely something else.

If you don't manage to load / save with the above, you may need to check your back-end (connector, your JSON format, etc...) and ask for it on a separate question.