How can I create an HTML table from JSON data returned from SQL.JS?

Question

I am trying to recreate the awesome online sqlite viewer, maintained by Lovasoa, in electron and am having trouble displaying the returned JSON data as an HTML table. The section of code I am working on looks like this:

    $(document).ready(function(){
    //displays object as string                  
    var res = db.exec("SELECT * FROM lorem"); 
    document.getElementById('dbrows').innerHTML = JSON.stringify(res);

        //convert to table
        var tbl_body = "";
        var odd_even = false;
        $.each(res, function() {
        var tbl_row = "";
        $.each(this, function(k , v) {
        tbl_row += "<td>"+v+"</td>";
     })
     tbl_body += "<tr class=\""+( odd_even ? "odd" :  "even")+"\">"+tbl_row+"</tr>";
    odd_even = !odd_even;               
  })
$("#table").html(tbl_body);
 });

My table is coming out all screwed up. Let me break down what I am trying to do.

    var res = db.exec("SELECT * FROM lorem"); 
    document.getElementById('dbrows').innerHTML = JSON.stringify(res); 

This part of the code returns a string exactly as one would expect from the sql.js documentation. Here is the relevant code from the sql.js README on github.

    var res = db.exec("SELECT * FROM hello");
/*
[
    {columns:['a','b'], values:[[0,'hello'],[1,'world']]}
]
*/

My problem is trying to get that returned value into a nice html table. I can see in the javascript for the online slq.js program the section of code that appears to do this:

// Create an HTML table
var tableCreate = function () {
  function valconcat(vals, tagName) {
    if (vals.length === 0) return '';
    var open = '<'+tagName+'>', close='</'+tagName+'>';
    return open + vals.join(close + open) + close;
  }
  return function (columns, values){
    var tbl  = document.createElement('table');
    var html = '<thead>' + valconcat(columns, 'th') + '</thead>';
    var rows = values.map(function(v){ return valconcat(v, 'td'); });
    html += '<tbody>' + valconcat(rows, 'tr') + '</tbody>';
      tbl.innerHTML = html;
    return tbl;
  }
}();

But I don’t understand how to link this up with anything returned by the db.exec function in the documentation. I am very new to javascript so I imagine this might be an obvious problem to someone with more experience. I have also been attempting to use the code in Cleric’s example (https://stackoverflow.com/a/10301494). However, my modified code returns everything on one line and does not put the data in neat individual rows. Here is my modified code:

  $(document).ready(function(){
        //displays object as string                  
        var res = db.exec("SELECT * FROM lorem"); 
        document.getElementById('dbrows').innerHTML = JSON.stringify(res);  //just to prove the db.exec works. 

       //convert to table
        var tbl_body = "";
        var odd_even = false;
        $.each(res, function() {
        var tbl_row = "";
        $.each(this, function(k , v) {
        tbl_row += "<td>"+v+"</td>";
    })
    tbl_body += "<tr class=\""+( odd_even ? "odd" : "even")+"\">"+tbl_row+"</tr>";
    odd_even = !odd_even;               
})
$("#table").html(tbl_body);
   });  

The repository with my project (forked from codewise's example) can be found here. Thanks for the help.

Answer 1

Instead of doing all this manual work, you can simply use one of the existing jQuery plugins like this:

You can bind it directly to your ajax call source as well.

Step 1 - include javascript and css:

<script src="jquery.min.js"></script>
<script src="simple.datagrid.js"></script>
<link rel="stylesheet" href="simple.datagrid.css">

Step 2 - html:

<table id="demo-table" data-url="/fruit_data/">
  <thead>
    <tr>
      <th>Name</th>
      <th>Latin name</th>
    </tr>
  </thead>
</table>

Step 3 - javascript:

$('#demo-table').simple_datagrid();

Or:

var res = db.exec("SELECT * FROM hello");
var data = JSON.stringify(res);

And then:

$('#demo-table').simple_datagrid(
{
   data: data
});