dynamic table html from json schema

Updated : Nov 20, 2019 in Articles

Dynamic table from json schema

In this article we will learn  how to create a Dynamic table from JSON schema,  the schema is coming to SQL or MySQL by using key/property from JSON we will create dynamic  HTML a table

Introduction:

JSON schema structure is a collection of key/property, JSON stands for “JavaScript Object Notation” it is a simple data interchange format. 

The requirement for creating dynamic HTML table from a JSON object

  1. Create a table in your database and return the entire table format as JSON file 
  2. implementing a javascript function for creating Dynamic  table from a JSON object

Step 1: Create a table in your database and return the entire table format as JSON file 

Open your SQL server or my SQL server create a table in your database name like Customers and dump some dummy data in your table

If you are using SQL server executes the given below SQL query 

CREATE TABLE [dbo].[Customer](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](40) NOT NULL,
	[LastName] [nvarchar](40) NOT NULL,
	[City] [nvarchar](40) NULL,
	[Country] [nvarchar](40) NULL,
	[Phone] [nvarchar](20) NULL,
	[Message] [nvarchar](200) NULL
	)

Create one stored procedure and  return table data as a JSON format by using given below SQL query

Create proc [dbo].[tablefields](@tablename nvarchar(30))
as
begin
declare @tablfields nvarchar(MAX);
set @tablfields=('select *from '[email protected]+' FOR JSON AUTO')
exec(@tablfields);
end
GO


Execute the above procedure by passing the table name  as parameter 

Exec  [dbo].[tablefields]
@tablename='Customer'

It  will return clear format of  JSON schema see given JSON format

[
  {
    "Id": 1,
    "FirstName": "Maria",
    "LastName": "Anders",
    "City": "Berlin",
    "Country": "Germany",
    "Phone": "030-0074321"
  },
  {
    "Id": 2,
    "FirstName": "Ana",
    "LastName": "Trujillo",
    "City": "México D.F.",
    "Country": "Mexico",
    "Phone": "(5) 555-4729"
  },
  {
    "Id": 3,
    "FirstName": "Antonio",
    "LastName": "Moreno",
    "City": "México D.F.",
    "Country": "Mexico",
    "Phone": "(5) 555-3932"
  },
  {
    "Id": 4,
    "FirstName": "Thomas",
    "LastName": "Hardy",
    "City": "London",
    "Country": "UK",
    "Phone": "(171) 555-7788"
  },
  {
    "Id": 5,
    "FirstName": "Christina",
    "LastName": "Berglund",
    "City": "Luleå",
    "Country": "Sweden",
    "Phone": "0921-12 34 65"
  },
  {
    "Id": 6,
    "FirstName": "Hanna",
    "LastName": "Moos",
    "City": "Mannheim",
    "Country": "Germany",
    "Phone": "0621-08460"
  },
  {
    "Id": 7,
    "FirstName": "Frédérique",
    "LastName": "Citeaux",
    "City": "Strasbourg",
    "Country": "France",
    "Phone": "88.60.15.31"
  },
  {
    "Id": 8,
    "FirstName": "Martín",
    "LastName": "Sommer",
    "City": "Madrid",
    "Country": "Spain",
    "Phone": "(91) 555 22 82"
  },
  {
    "Id": 9,
    "FirstName": "Laurence",
    "LastName": "Lebihan",
    "City": "Marseille",
    "Country": "France",
    "Phone": "91.24.45.40"
  }
  
]


Create an HTML page  Add the following Html code in your HTML page

<br /><br />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>l
<script src="assets/DynamicTableJs.js"></script>
<link href="assets/DynamicTableCss.css" rel="stylesheet" />

<div class="container">
    <h1><a href="https://tutorialshelper.com/"> Dynamic  table from json schema</a></h1>
    <div class="tables" id="showData"></div>
</div>
<script>
    $(document).ready(function () {       
        dynamic_table("http://dummy.restapiexample.com/api/v1/employees");
    });
</script>

Step2:  implementing a javascript function for creating Dynamic  table from JSON schema

Add in your project js file and create javascript function name like dynamic_table();

If we want to create a Dynamic  table from JSON object must and should be we will  read that JSON object by using ajax call

We will pass to dynamic_table() javascript function JSON created  URL as a parameter ( controller or directly some dummy  test APIs) 

After ajax success, we will extract JSON  values for HTML table headers then after we will create HTML dynamic table by using JSON Schema

Following the given below implemented javascript function for creating Dynamic  HTML table from JSON schema 

function dynamic_table(url) {
    $.ajax({
        url: url,
        data: {
            format: 'json'
        },
        error: function () {
            $('#info').html('<p>An error has occurred</p>');
        },
        dataType: 'json',
        success: function (data) {           
            var col = [];
            window.cols = col;
            for (var i = 0; i < data.length; i++) {
                for (var key in data[i]) {
                    if (col.indexOf(key) === -1) {
                        col.push(key);
                    }
                }
            }
            col.push("Actons");          
            var table = document.createElement("table");
            table.setAttribute('class', 'table blueTable');      
            var tr = table.insertRow(-1);                   
            for (var i = 0; i < col.length; i++) {
                var th = document.createElement("th");     
                th.innerHTML = col[i];
                tr.appendChild(th);
            }        
            for (var i = 0; i < data.length; i++) {
                tr = table.insertRow(-1);
                for (var j = 0; j < col.length; j++) {
                    var tabCell = tr.insertCell(-1);
                    tabCell.innerHTML = data[i][col[j]];
                }
                var d = data[i].id;
                tabCell.innerHTML = '<a href="#" onclick="return EditTable(' + data[i].id + ' )">Edit</a> | <a href="#" onclick="DeleteTable(' + data[i].id + ')">Delete</a></td>'
            }       
            var divContainer = document.getElementById("showData");
            divContainer.innerHTML = "";
            divContainer.appendChild(table);
            jQuery('.tables').before('<input type="submit" onclick="return CreateDynamicModelPopup();" class="addme" value="Add New!"></div>');
        },
        type: 'GET'
    });
};

table CSS add given below CSS file in your project

.col-sm-12.modeldata {
    padding-top: 19px;
    padding-bottom: 7px;
}

.addme {
    margin-bottom: 11px;
    background-color: #d0e4f5;
    border: 0px solid #ccc;
    padding: 12px;
    box-shadow: 0 0 1px black;
}

.modal-body {
    padding: 0px !important;
}

.modal-header {
    background: #a2c1dc;
}

table.blueTable {
    border: 1px solid #1C6EA4;
    background-color: #EEEEEE;
    width: 100%;
    text-align: left;
    border-collapse: collapse;
}

    table.blueTable td, table.blueTable th {
        border: 1px solid #AAAAAA;
        padding: 3px 2px;
    }

    table.blueTable tbody td {
        font-size: 13px;
    }

    table.blueTable tr:nth-child(even) {
        background: #D0E4F5;
    }

    table.blueTable thead {
        background: #1C6EA4;
        background: -moz-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        background: -webkit-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        background: linear-gradient(to bottom, #5592bb 0%, #327cad 66%, #1C6EA4 100%);
        border-bottom: 2px solid #444444;
    }

        table.blueTable thead th {
            font-size: 15px;
            font-weight: bold;
            color: #FFFFFF;
            border-left: 2px solid #D0E4F5;
        }

            table.blueTable thead th:first-child {
                border-left: none;
            }

    table.blueTable tfoot {
        font-size: 14px;
        font-weight: bold;
        color: #FFFFFF;
        background: #D0E4F5;
        background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
        background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
        background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);
        border-top: 2px solid #444444;
    }

        table.blueTable tfoot td {
            font-size: 14px;
        }

        table.blueTable tfoot .links {
            text-align: right;
        }

            table.blueTable tfoot .links a {
                display: inline-block;
                background: #1C6EA4;
                color: #FFFFFF;
                padding: 2px 8px;
                border-radius: 5px;
            }

Try run your HTML page …

Tips and Tricks:

If you are using APIs in your project you will pass API  to javascript function as a parameter, Instead of creating SQL table and return JSON simple you will use some test APIs also

Rest Apis for text javascript function :

<script>
    $(document).ready(function () {
        dynamic_table("http://dummy.restapiexample.com/api/v1/employees");
       //dynamic_table("https://jsonplaceholder.typicode.com/posts");
    });
</script>

output:

Dynamic table from json schema

View Demo

recommended the previous article for more details https://tutorialshelper.com/dynamic-ui-from-json-object-using-web-api-with-jquery/