shapoglyk shapoglyk - 1 month ago 19
SQL Question

jQuery Datatable make columns from data

I have a following SQL query:

select created, sum(count), count(distinct `source_host`) from monitor where external_host='facebook.com' group by created;


Which gives data, that I represent in json:

[
{
"Created": "2017-08-17",
"SourceHostCount": 130,
"Count": 2
},
{
"Created": "2017-08-18",
"SourceHostCount": 76,
"Count": 2
},
{
"Created": "2017-08-19",
"SourceHostCount": 78,
"Count": 2
}
]


But in template I want to have table which looks like that:

| | 2017-08-17 | 2017-08-18 | 2017-08-19 |
|-----------------|------------|------------|------------|
| SourceHostCount | 130 | 76 | 78 |
| Count | 2 | 2 | 2 |


So the columns are needs to be a values from Created field and rows are the other fields.
Is there any way how I can do it by using jQuery Datable?

Answer Source

You will have to "format" your data a little for DataTable.

The rows are arrays in a container array.
The column titles are objects in a container array.

Below are two similar "options" to format it from the array of objects you created from PHP.
But you also could format your data correctly for DataTable directly on server side...

If so, have a look at this example from the documentation.

If you have to use the data as you posted it, here it is (on two rows)

var data = [
  {
    "Created": "2017-08-17",
    "SourceHostCount": 130,
    "Count": 2
  },
  {
    "Created": "2017-08-18",
    "SourceHostCount": 76,
    "Count": 2
  },
  {
    "Created": "2017-08-19",
    "SourceHostCount": 78,
    "Count": 2
  }
];

// Prepare the data to DataTable format
var row0 = [{title:""}];
var row1 = ["SourceHostCount"];
var row2 = ["Count"];

for(i=0;i<data.length;i++){
  row0.push({title:data[i].Created}); 
  row1.push(data[i].SourceHostCount);  
  row2.push(data[i].Count);
}

var datatableData = [row1, row2];

$("#result").DataTable({
  data: datatableData,
  columns: row0
});
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>

<table id="result">
</table>


If you need all the data on ONE row, here is a slightly different snippet:

var data = [
  {
    "Created": "2017-08-17",
    "SourceHostCount": 130,
    "Count": 2
  },
  {
    "Created": "2017-08-18",
    "SourceHostCount": 76,
    "Count": 2
  },
  {
    "Created": "2017-08-19",
    "SourceHostCount": 78,
    "Count": 2
  }
];

var row0 = [{title:""}];
var row1 = ["SourceHostCount<br>Count"];

for(i=0;i<data.length;i++){
  row0.push({title:data[i].Created}); 
  row1.push(data[i].SourceHostCount+"<br>"+data[i].Count);  
}

var datatableData = [row1];

$("#result").DataTable({
  data: datatableData,
  columns: row0
});
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>

<table id="result">
</table>