current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to display the dataset inside the cartoDB as HTML table. When i give request to the postgre database by SQL API, it is returning as the format (json) below in the snapshot. I want the returning dataset to be displayed as HTML table in the browser. I had tried with the below code but it is displaying table for var myList=[{"name" : "abc", "age" : 50}, {"age" : "25", "hobby" : "swimming"}, {"name" : "xyz", "hobby" : "programming"}];

But its not displaying for the below format.

enter image description here

And code i had tried is 

<script type="text/javascript">
        data1 = "transit_point_region_1";
        json_link = 'http://development.localhost.lan:8080/api/v2/sql?q=SELECT * FROM ' + data1;
        var data = $.getJSON(json_link);
        // Builds the HTML Table out of data.
        function buildHtmlTable() {
            var columns = addAllColumnHeaders(data);
            for (var i = 0 ; i < data.length ; i++) {
                var row$ = $('<tr/>');
                for (var colIndex = 0 ; colIndex < columns.length ; colIndex++) {
                    var cellValue = data[i][columns[colIndex]];
                    if (cellValue === null) { cellValue = ""; }
                    row$.append($('<td/>').html(cellValue));
                }
                $("#excelDataTable").append(row$);
            }
        }

    function addAllColumnHeaders(data). {
        var columnSet = [];
        var headerTr$ = $('<tr/>');
        for (var i = 0 ; i < data.length ; i++) {
            var rowHash = data[i];
            for (var key in rowHash) {
                if ($.inArray(key, columnSet) === -1){
                    columnSet.push(key);
                    headerTr$.append($('<th/>').html(key));
                }
            }
        }
        $("#excelDataTable").append(headerTr$);
        return columnSet;
    }
</script>
</head>
<body onLoad="buildHtmlTable()">
    <table id="excelDataTable" border="1">
        </table>

share|improve this question

1 Answer 1

up vote 1 down vote accepted

There are a couple issues here. First, the JSON object shown in your screenshot has a slightly different format than the one expected by your code. Notice that at the beginning, the first key is rows, which contains all the results as an array (in square brackets). You can extract the results in the format you need by assigning data.rows to a new variable (i.e. new_data = data.rows or the like).

Here is an example fiddle (forked from the answer to this stackoverflow question, which appears to be the source of your code):

http://jsfiddle.net/nathansnider/ws5rL3vy/

It uses data in the same format as the object from your screenshot and shows that extracting the rows array allows the data to be used in the functions that follow.

The second issue is that $.getJSON is an asynchronous method and will not return data immediately. You should be using the appropriate syntax if you want your data to be accessible to the rest of your code. Typically, this is done as a callback function within the getJSON function itself. Your buildHtmlTable function should look something like this:

function buildHtmlTable() {
    $.getJSON(json_link, function (result_data) {
        var data = result_data.rows;
        var columns = addAllColumnHeaders(data);

        for (var i = 0; i < data.length; i++) {
            var row$ = $('<tr/>');
            for (var colIndex = 0; colIndex < columns.length; colIndex++) {
                var cellValue = data[i][columns[colIndex]];

                if (cellValue == null) {
                    cellValue = "";
                }

                row$.append($('<td/>').html(cellValue));
            }
            $("#excelDataTable").append(row$);
        }
    });
}

Here is a fiddle using getJSON to grab a file from Dropbox, which contains the same data as in the previous example:

http://jsfiddle.net/nathansnider/goe84e04/

share|improve this answer
    
Thank you nathansnider. – Farook Oct 29 at 4:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.