How to fill out datatables with stored data json variable

How to fill out datatables with stored data json variable

fael_christofanofael_christofano Posts: 13Questions: 2Answers: 0
edited January 2018 in Free community support

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?

Replies

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0
    edited January 2018

    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.

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0

    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);
    
                    }
                });
    
            }
    
        }
    });
    

    });

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0

    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.

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0
    edited January 2018

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

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0

    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



    1.png 16.3K
    2.png 15.1K
    3.png 22.8K
    4.png 50.3K
  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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_christofanofael_christofano Posts: 13Questions: 2Answers: 0

    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.

  • kthorngrenkthorngren Posts: 20,147Questions: 26Answers: 4,736

    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

This discussion has been closed.