How to fill out datatables with stored data json variable

fael_christofano

I have one ajax query that store in variable 'msg' the data of one query in a table of database.

$.ajax({
    type: 'POST',
    dataType: 'html',
    url: page,
    data: {idtable: idtable},
    success: function (msg) {
        msg = $.parseJSON(msg);
    }
});

I need to fill out the table(using plugin DataTables) in front-end with data this variable, How to do this?

kthorngren

This doc details the data types and source. It has examples of how to do what you want:
https://datatables.net/manual/data/

Kevin

fael_christofano

Hi Kevin, thanks for answering..
I studied the link you sent me, but I still not get to implement.
In the DataTables manual not show how to do one query in database, after generate the data for JSON and finaly show in Datatable.
I don´t understand what information is this:
ajax: '/api/myData'
Where does 'myData' come from?

I did so:

$(document).ready(function() {
$('#index-servico').DataTable({
    language: {
        url: '../includes/datatable_ptbr.json'
    },
    responsive: true,
    ajax: {
        url: 'api/data',
        dataFilter: function (data) {
            var opRadio = $('#filtroStatus:checked').val();
            var page = "busca_servico_por_status.php";
            $.ajax({
                type: 'POST',
                dataType: 'html',
                url: page,
                data: {idstatus: opRadio},
                success: function (msg) {
                    return JSON.stringify(msg);
                   // msg = $.parseJSON(msg);
                }
            });

        }

    }
});

});

But this does not work.

kthorngren

This is the ajax doc:
https://datatables.net/manual/ajax

Here are some ajax sourced data example:
https://datatables.net/examples/ajax/index.html

Why do you have an ajax request in the dataFilter option of the Datatables ajax option?

Maybe that's the source of confusion. Maybe you just need this:

$(document).ready(function() {

var opRadio = $('#filtroStatus:checked').val();
var page = "busca_servico_por_status.php";

$('#index-servico').DataTable({
    language: {
        url: '../includes/datatable_ptbr.json'
    },
    responsive: true,
    ajax: {
                type: 'POST',
                url: page,
                data: {idstatus: opRadio},
                success: function (msg) {
                    return JSON.stringify(msg);
                   // msg = $.parseJSON(msg);
                }
            
 
        }
 
    });
});

Kevin

fael_christofano

Hi Kevin, thanks very much your atention. I am bugged, I can´t show data in DataTables. In my JavaScript I get the data from the query in the bank that returns in json format, but not show. My browser show the message: 'DataTables warning: table id=index-servico - Ajax error. For more information about this error, please see http://datatables.net/tn/7'
Look my code on more time, please.

My front-end

<table id="index-servico">
                            <thead>
                                <tr>
                                    <th>Cliente</th>
                                    <th>Carro</th>
                                    <th">Data</th>
                                    <th style="width: 150px">Ações</th>
                                </tr>
                            </thead>
                            <tfoot>
                            <tr>
                                <th>Cliente</th>
                                <th>Carro</th>
                                <th>Data</th>
                                <th>Ações</th>
                            </tr>
                            </tfoot>
</table>

In Server-side I have th file 'busca_servico_por_status.php'

<?php
     include_once 'Conexao.php';
      class Servico {
              private $conn;
              public function __construct() {
                      $this->conn = new Conexao();
              }
        public function busca()
         {
                try {
                     $sql = "SELECT ca.carro as carro, ca.placa as placa, cl.nome as cliente, s.*
                            FROM carro ca, cliente cl, servico s 
                            WHERE s.carro_id = ca.id and  s.cliente_id = cl.id";
                     $create = $this->conn->getInstancia()->prepare($sql);
                     $create->execute();
                     return $create->fetchAll(PDO::FETCH_OBJ);
                 } catch (Exception $ex) {
                      return false;
                }
         }
  }
$servico = new Servico();
echo json_encode($servico->busca());

In my JavaScript

$(document).ready(function() {
var opRadio = $('#filtroStatus:checked').val();
var page = "busca_servico_por_status.php";

$('#index-servico').DataTable({
    language: {
        url: '../includes/datatable_ptbr.json'
    },
    responsive: true,
    ajax: {
        url: 'api/data',
        dataFilter: function (data) {

            $.ajax({
                type: 'POST',
                dataType: 'html',
                url: page,
                data: {idstatus: opRadio},
                success: function (msg) {
                    return JSON.stringify(msg);

                }
            });

        }

    }
});

});

kthorngren

Did you follow the instructions at the link in the error?
http://datatables.net/tn/7

Why do you have this?

    ajax: {
        url: 'api/data',
        dataFilter: function (data) {
.....

Kevin

fael_christofano

I have that because it is shown in https://datatables.net/manual/server-side

The instructions about my error, I don´t understand. I don´t received the file array.txt , how show in the figures.

I believe that I'm developing something that is common for developers who use dataTables, but I'm starting in dataTable and all its working I'm not able to absorb immediately.
I need a start, an example of a simple code of how to make a request in the database using php, return a Json and feed the datatables with that json.

Do you have something that I can use as guidance?

Thanks very much.

kthorngren

Did you try with the code I posted above?

$(document).ready(function() {
 
var opRadio = $('#filtroStatus:checked').val();
var page = "busca_servico_por_status.php";
 
$('#index-servico').DataTable({
    language: {
        url: '../includes/datatable_ptbr.json'
    },
    responsive: true,
    ajax: {
                type: 'POST',
                url: page,
                data: {idstatus: opRadio},
                success: function (msg) {
                    return JSON.stringify(msg);
                   // msg = $.parseJSON(msg);
                }
             
  
        }
  
    });
});

You are nesting ajax requests which I suspect is not what you are wanting to do. But it looks like you want to pass some parameters in the request to your server script which the above should do.

Kevin

fael_christofano

I tried wit the your code, when I debug the data arrive in 'msg' variable, but do not populate the DataTable:

kthorngren

Two things:

1. You probably don't need the success function unless you are going to do something in addition to returning the data. Looks like the data is returned as a JSON string. Using return JSON.stringify(msg); just encapsulates the JSON string in another string. Either remove the success function or just use return msg;.

2. Since you are returning an array of objects you will need to use columns.data to define your columns. Please also see the following:
   https://datatables.net/manual/data/

Kevin

fael_christofano

Guy, I don´t know...
I did follow the exemple: https://datatables.net/examples/ajax/objects.html
Only thing is different is the ajax property, that I put the request ajax shown the you in before posts.
In this exemple, is used um .txt file.
I put and removed 'success' and it does´t work

kthorngren

Looks like you are close....

columns.data is case sensitive. Looks like you need this:

columns: [
  {data: 'cliente'},
  {data: 'carro'},
  {data: 'data'},
  {data: 'acoes'},   ???
]

I'm not sure what the last one should be. But they need to match the keys in your JSON response.

Kevin

fael_christofano

OK, I understanded that the 'columns' in AJAX is to show in header of table, I did not think that it was of the headers of columns os query.
The columns 'acoes' is one columns that not show data of query. It is one extra column to permisse action of user. In it table é put on button.

Still not work, but I did read in the end this page: https://datatables.net/manual/ajax.

"For completeness of our Ajax loading discussion, it is worth stating that at this time DataTables unfortunately does not support configuration via Ajax. This is something that will be reviewed in future, however, JSON does not provide an option to represent Javascript functions which can be exceptionally useful in a DataTables configuration, therefore Ajax loaded configuration could not use all of the options DataTables makes available."

I need to generate one txt file.

kthorngren

If you want to provide Datatables configuration in the ajax response then you will need to execute the ajax request outside of the Datatables init code. This example shows how to build the table headers using information from the ajax request:

http://live.datatables.net/fafuyeyu/55/edit

It simply uses the keys from the JSON for each column. You could return an object that contain the table headers and other DT config items.

Kevin