jQuery Datatable make columns from data

Question

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?

Do you need the values on ONE row ?

Louys Patrice Bessette
– Louys Patrice Bessette

2017-08-19 05:54:01 +00:00
Commented Aug 19, 2017 at 5:54 — Louys Patrice Bessette
– Louys Patrice Bessette, Commented Aug 19, 2017 at 5:54
@LouysPatriceBessette, yes, only on Created field

belek
– belek

2017-08-19 08:55:35 +00:00
Commented Aug 19, 2017 at 8:55 — belek
– belek, Commented Aug 19, 2017 at 8:55

Louys Patrice Bessette · Accepted Answer · 2017-08-19 06:08:21Z

1

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>

edited Aug 19, 2017 at 6:08

answered Aug 19, 2017 at 5:27

Louys Patrice Bessette

34k7 gold badges40 silver badges69 bronze badges

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

4 Comments

Death-is-the-real-truth Over a year ago

OP said this:- Is there any way how I can do it by using jQuery Datatable? and he added tag of it also.Please read question carefully

Louys Patrice Bessette Over a year ago

@AlivetoDie: I fixed it.

Death-is-the-real-truth Over a year ago

I think OP want each row have to show these two values.Add one more row with same data and see your code worked or not? Means to say two rows with two-two entries in each row

Louys Patrice Bessette Over a year ago

mmm... Maybe. I asked him.

Collectives™ on Stack Overflow

jQuery Datatable make columns from data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related