How to create a table from a json api using HTML tables

Question

We are using an api to retrieve data from MySQL database and turning it into a json file type. What would be the best way to get that data into an HTML table.

This is the api: https://nauticmc.com/dev/top.php?type=kills&start=0&end=9

Depending on what you change the type to it will change ascending order of the data I.E Kills will display the heights amount of kills first.

$(document).ready(function () {
    var json = [{"uuid":"ba6df9ff-84fa-464b-a231-d36782a7fdba","username":"zFrost87","kills":"19","deaths":"6","broken":"34","placed":"34","fish":"15","onlinetime":"9493","mobskilled":"6","crops":"1"},{"uuid":"b07762f3-38a4-45b7-9f21-6147704aee4b","username":"frechette456","kills":"3","deaths":"11","broken":"33","placed":"61","fish":"0","onlinetime":"1251","mobskilled":"1","crops":"7"},{"uuid":"83af0a86-7c09-4c32-8fa5-65db3c50b4cf","username":"MineManFrost","kills":"1","deaths":"2","broken":"16","placed":"1","fish":"0","onlinetime":"238","mobskilled":"0","crops":"0"},{"uuid":"71cb5612-d586-4390-ad78-05d295d6d2bd","username":"Nikolassparrow","kills":"0","deaths":"2","broken":"1","placed":"2","fish":"0","onlinetime":"30","mobskilled":"0","crops":"0"},{"uuid":"c34f51b5-8f2a-4e08-8b83-99d5faf21ea9","username":"SetupS1CK","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"81","mobskilled":"0","crops":"0"},{"uuid":"80212863-9466-4a4b-852e-5812e77d075b","username":"PickCottonNig","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"835","mobskilled":"0","crops":"0"},{"uuid":"a64de20f-91e7-4bb0-80db-4f7f1614d693","username":"OstenGaming","kills":"0","deaths":"0","broken":"1","placed":"0","fish":"0","onlinetime":"213","mobskilled":"0","crops":"0"},{"uuid":"25c60209-9fd4-42b8-a2b7-d78e01c0b3ae","username":"OpTc","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"9","mobskilled":"0","crops":"0"},{"uuid":"3a1dc4c7-e9ec-425e-ad50-54ae37c5f1a7","username":"AutoDolphin","kills":"0","deaths":"11","broken":"20","placed":"1","fish":"0","onlinetime":"444","mobskilled":"0","crops":"0"}];
    var tr;
    for (var i = 0; i < json.length; i++) {
        tr = $('<tr/>');
        tr.append("<td>" + json[i].username + "</td>");
        tr.append("<td>" + json[i].kills + "</td>");
        tr.append("<td>" + json[i].deaths + "</td>");
        tr.append("<td>" + json[i].broken + "</td>");
        tr.append("<td>" + json[i].placed + "</td>");
        tr.append("<td>" + json[i].fish + "</td>");
        tr.append("<td>" + json[i].onlinetime + "</td>");
        tr.append("<td>" + json[i].mobskilled + "</td>");
        tr.append("<td>" + json[i].crops + "</td>");
        $('table').append(tr);
    }
});

I am using Local JSON for now because Ajax does not allow access cross domain but I would parse it into a var to get it.

I also want people to be able to see what data is on top by corresponding arrows in each column I tried to use datatables.js but that did not work so well

This is just the table without any js or json

https://strixdesigns.com/Stats1/ < I want the arrows to work like this

Your above code already inserts the JSON data into the table. What exactly are you struggling with - retrieving it from the server with AJAX? — Obsidian Age
– Obsidian Age, Commented Jul 4, 2017 at 0:56
When I look at my JS console on your example page, I'm seeing a mixed content error. This could be causing the problem. You need to load jQuery from a secure (https) location. Grab a url from here. — Andy Hoffman
– Andy Hoffman, Commented Jul 4, 2017 at 1:43

Poldo · Accepted Answer · 2017-07-04 01:27:08Z

1

Why dont use datatable and set the options like this your thead field must the same on your json field count

HTML

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Username</th>
            <th>Kills</th>
            ....
        </tr>
    </thead>

</table>

JQUERY

$(document).ready(function() {
    $('#example').DataTable( {
        "ajax": {
            "url": "https://nauticmc.com/dev/top.php?type=kills&start=0&end=9",
           "type": "POST",
           "data":{
                     'type': 'kills',
                     'start': 0,
                     'end': 9
                 }
        },
        "columns": [
            { "data": "username " },
            { "data": "kills " },
            ...
        ]
    } );
} );

check documentation here: https://datatables.net/examples/ajax/custom_data_flat.html

edited Jul 4, 2017 at 1:27

answered Jul 4, 2017 at 1:01

Poldo

1,9321 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Poldo Over a year ago

@ThatPurpleGuy Please kindly up if this helped you.

ThatPurpleGuy Over a year ago

Im pretty sure that i cant get json file from that site because it is just stuck on loading gyazo.com/51d9d3cff1ee42f9a4752778c8f17a71

Poldo Over a year ago

@ThatPurpleGuy if type = "post" not working try type="get"

ThatPurpleGuy Over a year ago

So here is what i got for an error DataTables warning: table id=myTable - Invalid JSON response. For more information about this error, please see datatables.net/tn/1 nauticmc.com/stats

Poldo Over a year ago

@ThatPurpleGuy please check your php is not returning json data

|

Andy Hoffman · Accepted Answer · 2017-07-04 01:36:45Z

Another way to do this is to store the desired order in an array and add a nested loop to your logic. The advantage here is that you can change the order to whatever you'd like (username last, third, etc.) and your markup will not have to change to accommodate the reordering of fields.

$(document).ready(function () {
    var json = [{"uuid":"ba6df9ff-84fa-464b-a231-d36782a7fdba","username":"zFrost87","kills":"19","deaths":"6","broken":"34","placed":"34","fish":"15","onlinetime":"9493","mobskilled":"6","crops":"1"},{"uuid":"b07762f3-38a4-45b7-9f21-6147704aee4b","username":"frechette456","kills":"3","deaths":"11","broken":"33","placed":"61","fish":"0","onlinetime":"1251","mobskilled":"1","crops":"7"},{"uuid":"83af0a86-7c09-4c32-8fa5-65db3c50b4cf","username":"MineManFrost","kills":"1","deaths":"2","broken":"16","placed":"1","fish":"0","onlinetime":"238","mobskilled":"0","crops":"0"},{"uuid":"71cb5612-d586-4390-ad78-05d295d6d2bd","username":"Nikolassparrow","kills":"0","deaths":"2","broken":"1","placed":"2","fish":"0","onlinetime":"30","mobskilled":"0","crops":"0"},{"uuid":"c34f51b5-8f2a-4e08-8b83-99d5faf21ea9","username":"SetupS1CK","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"81","mobskilled":"0","crops":"0"},{"uuid":"80212863-9466-4a4b-852e-5812e77d075b","username":"PickCottonNig","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"835","mobskilled":"0","crops":"0"},{"uuid":"a64de20f-91e7-4bb0-80db-4f7f1614d693","username":"OstenGaming","kills":"0","deaths":"0","broken":"1","placed":"0","fish":"0","onlinetime":"213","mobskilled":"0","crops":"0"},{"uuid":"25c60209-9fd4-42b8-a2b7-d78e01c0b3ae","username":"OpTc","kills":"0","deaths":"0","broken":"0","placed":"0","fish":"0","onlinetime":"9","mobskilled":"0","crops":"0"},{"uuid":"3a1dc4c7-e9ec-425e-ad50-54ae37c5f1a7","username":"AutoDolphin","kills":"0","deaths":"11","broken":"20","placed":"1","fish":"0","onlinetime":"444","mobskilled":"0","crops":"0"}];
    var tr, th;
    var order = ['username', 'kills', 'deaths', 'broken', 'placed', 'fish', 'onlinetime', 'mobskilled', 'crops'];
  
    for (var i = 0; i < json.length; i++) {
      tr = $('<tr/>');
      th = $('<tr/>');
      
      if (i === 0) { // create table headers               
        for (var j = 0; j < order.length; j++) { 
          th.append("<th>" + order[j] + "</th>");
          $('table').append(th)
        }         
      }     
      
      for (var j = 0; j < order.length; j++) { 
        tr.append("<td>" + json[i][order[j]] + "</td>");
        $('table').append(tr);      
      }
    }
});

table {
  font-family:Arial, Helvetica, sans-serif;
  color:#666;
  font-size:12px;
  text-shadow: 1px 1px 0px #fff;
  background:#eaebec;
  margin:20px;
  border:#ccc 1px solid;
  border-radius:3px;
  box-shadow: 0 1px 2px #d1d1d1;
  display: block;
  max-width: 100%;
  overflow: auto;
}

table th {
  padding:21px 25px 22px 25px;
  border-top:1px solid #fafafa;
  border-bottom:1px solid #e0e0e0;
  background: #ededed;
  background: -webkit-gradient(linear, left top, left bottom, from(#ededed), to(#ebebeb));
  background: -moz-linear-gradient(top,  #ededed,  #ebebeb);
}
table th:first-child {
  text-align: left;
  padding-left:20px;
}
table tr:first-child th:first-child {
  border-top-left-radius:3px;
}
table tr:first-child th:last-child {
  border-top-right-radius:3px;
}
table tr {
  text-align: center;
  padding-left:20px;
}
table td:first-child {
  text-align: left;
  padding-left:20px;
  border-left: 0;
}
table td {
  padding:18px;
  border-top: 1px solid #ffffff;
  border-bottom:1px solid #e0e0e0;
  border-left: 1px solid #e0e0e0;

  background: #fafafa;
  background: -webkit-gradient(linear, left top, left bottom, from(#fbfbfb), to(#fafafa));
  background: -moz-linear-gradient(top,  #fbfbfb,  #fafafa);
}
table tr.even td {
  background: #f6f6f6;
  background: -webkit-gradient(linear, left top, left bottom, from(#f8f8f8), to(#f6f6f6));
  background: -moz-linear-gradient(top,  #f8f8f8,  #f6f6f6);
}
table tr:last-child td {
  border-bottom:0;
}
table tr:last-child td:first-child {
  border-bottom-left-radius:3px;
}
table tr:last-child td:last-child {
  border-bottom-right-radius:3px;
}
table tr:hover td {
  background: #f2f2f2;
  background: -webkit-gradient(linear, left top, left bottom, from(#f2f2f2), to(#f0f0f0));
  background: -moz-linear-gradient(top,  #f2f2f2,  #f0f0f0);  
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<table></table>

This works but i am not using Datatables js so there is not a way for me to sort the data more easily

Collectives™ on Stack Overflow

How to create a table from a json api using HTML tables

2 Answers 2

HTML

JQUERY

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

HTML

JQUERY

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related