dataTable, ajax, json

silens

Hola, tengo esta funcion ajax

$(document).ready (function() {
$.ajax({
url: "php/identidades.php",
success : function(data) {
var jsonObject = JSON.parse(data);//A la variable le asigno el json decodificado

    $('#tbl_entidad').dataTable( {
        data : jsonObject,

        columns: [
                  {"data" : "id"},
                  {"data" : "name"},
                  {"data" : "cif"}              
                  ],

    });
}

});});

Quiero usar Datatable.
Pero no funciona, Que puede estar mal?
Muchas Gracias

rf1234

Well take a look at the object, e.g. through a dump or in your debugger.
I suspect you need to change this:

$(document).ready (function() {
    $.ajax({
        url: "php/identidades.php",
        success : function(data) {
            var o = JSON.parse(data);//A la variable le asigno el json decodificado
            $('#tbl_entidad').dataTable( {
                data : o, 
                columns: [
                    {"data" : "o.id"},
                    {"data" : "o.name"},
                    {"data" : "o.cif"}             
                ],
            });
        }        
    });
});

silens

('''
Muchas gracias, dice que la tabla no tiene registros....No data avaiable in table

Esta es la estructura del Json. Que puede ser???
Yo pienso que lo que esta mal es la llamada a los registros.
Muchas gracias.
''')

rf1234

in you screenshot I see that the object is called "JSON", not "o"?!

is it rather "o.JSON.ent[0].id" ??

rf1234

Maybe another idea if you can't find the solution: Change your approach more according to the "data tables standard" (if it exists ... sometimes data tables is maybe a bit too flexible).

Here is what I believe would be closer to the standard. I would code it like this:

$(document).ready (function() {    
    var tblEntidad = $('#tbl_entidad').DataTable( {
        ajax: {
            url: 'php/identidades.php',
            type: 'POST'
        },
        columns: [  //or different depending on the structure of the object
                    {"data" : "o.JSON.ent[0].id"},
                    {"data" : "o.JSON.ent[0].name"},
                    {"data" : "o.JSON.ent[0].cif"}            
                ]
    });
});

and on the PHP side in "identidades.php"

....
echo json_encode( $result );
...

Please note the spelling of "DataTable". I think it is quite important that you spell it with a capital "D".

Take a look at this: https://datatables.net/reference/type/DataTables.Api

And if you take a look at the examples you'll find that it is always "DataTable" and never "dataTable" ...

silens

Thank you very much for your insistence, I am new to this and at the beginning it is complicated
This function works fine, now I want to weight the content into columns as does DataTable.

'''
function saldos(dni){
var parametros = {

    "cif": dni, 
};

$.ajax({
    data: parametros,
    url: "php/identidades.php",
    type: "POST",

    beforeSend: function(){

        },

    success: function(data){
        var myObj = JSON.parse(data);//A  la variable le asigno el json decodificado
        $("#contenido").html(myObj.ent[0].name );

    }       
});

}'''

rf1234

Sorry, I have no idea what you want to do with this. It was clearer to me in Spanish ... Are you using Google translate or one of those services? No funcionan estos servicios ...

CarlosEVelezF

Es posible esto mismo pero con django y python

silens

Le pido disculpas. Esta funcion trabaja correctamente pero yo no la quiero usar de esta manera, prefiero usarla con DataTable.
Necesito transformar esta funcion en una funcion que trabaje con Datatable, añadiendole columnas.
Dejo esta funcion que trabaja como ejemplo, necesito saber, que hay que cambiar en esta funcion para que trabaje con DataTable.

function saldos(dni){
var parametros = {

    "cif": dni, //A la variable empresa le asigno el cotenido de tabla
};

$.ajax({
    data: parametros,
    url: "php/identidades.php",// Aqui solo llamo al formulario. Esta web no inserta nada.
    type: "POST",

    beforeSend: function(){

        },

    success: function(data){
        var myObj = JSON.parse(data);//A  la variable le asigno el json decodificado
        $("#contenido").html(myObj.ent[0].name );

    }       
});

}

silens

Le pido disculpas. Esta funcion trabaja correctamente pero yo no la quiero usar de esta manera, prefiero usarla con DataTable.
Necesito transformar esta funcion en una funcion que trabaje con Datatable, añadiendole columnas.
Dejo esta funcion que trabaja como ejemplo, necesito saber, que hay que cambiar en esta funcion para que trabaje con DataTable.

function saldos(dni){
var parametros = {

    "cif": dni, //A la variable empresa le asigno el cotenido de tabla
};

$.ajax({
    data: parametros,
    url: "php/identidades.php",// Aqui solo llamo al formulario. Esta web no inserta nada.
    type: "POST",

    beforeSend: function(){

        },

    success: function(data){
        var myObj = JSON.parse(data);//A  la variable le asigno el json decodificado
        $("#contenido").html(myObj.ent[0].name );

    }       
});

}

Esta funcion que pongo abajo, no funciona, dice que no hay datos.

$(document).ready (function() {
$.ajax({
url: "php/identidades.php",
type: "POST",
success : function(data) {
var o = JSON.parse(data);//A la variable le asigno el json decodificado
$('#tbl_entidad').DataTable( {
data : o,
columns: [
{"data" : "o.ent[0].id"},
{"data" : "o.ent[0].name"},
{"data" : "o.ent[0].cif"}
],
});
}
});
});

rf1234

Well, I don't know exactly why your last function does not work but I wouldn't code it that way. Please revisit my detailed suggestion above.

I think you can make any of your functions work with datatables. All you need to do is make sure the JSON response is in the format that datatables expects.
You can render individual fields in one column but also arrays. No problem.

The easiest way is certainly to use both Data Tables and Editor at the front end and also on the server with PHP. That is what I am doing. I took that decision before I even started my project because I hate front end programming and wanted to have it as easy as possible. But there are additional advantages: You don't need to worry about the data format (e.g. what does my JSON object look like etc.), Editor does that for you. You don't need to code SELECT, INSERT, UPDATE and DELETE statements yourself. All you need to do is to set up one Editor instance on the back end site and one at the front end. An Editor license is cheap. It costs only 85 sterling which at the moment is less than €94 and just over $110.

Here is an example that handles a table with approx. 300,000 rows. As you can see Editor also has a server side processing feature handling all the sorting, paging and what have you very fast and efficiently on the server side in case your tables are large. It also has multi language support etc ...

Javascript:

function tblRate() {   
    var rateEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'actions.php?action=tblRate'
        },
        table: "#tblRate",
        fields: [ {
                label: "Reference Rate:",
                name:  "rate.ref_rate",
                type:  "select",
                options: refRateOptions
            }, {
                label: "Currency:",
                name:  "rate.currency",
                type:  "select",
                options: liborCurrencyOptions
            }, {
                label: "Rate Period:",
                name:  "rate.ref_rate_period",
                type:  "select",
                options: refRatePeriodOptions
            }, {
                label: "Rate Date:",
                name:  "rate.date",
                attr: {
                    class: dateMask
                },
                type:  "datetime",
                def:   function () { return today},
                format: 'L',
                opts:  {
                    showWeekNumber: true,
                    momentLocale: momentLocale
                }
            }, {
                label: "Rate %:",
                name:  "rate.rate"
            }
        ]        
    } );
    
    rateEditor
            .on('open', function(e, mode, action) {                
                maskDateTime();
            })
            .on( 'postSubmit', function ( e, json, data, action ) {
                if (json.error) {
                    if ( json.error.includes('1062 Duplicate entry') ) {
                       json.error = 'Sorry, this rate already exists!';
                    }
                }
            });

    var rateTable = $('#tblRate').DataTable( {
        dom: "Bfrltip",
        processing: true,
        serverSide: true,    //server side only works well with type "POST" !!!
        ajax: {
            url: 'actions.php?action=tblRate',
            type: 'POST'
        },
        pageLength: 20,
        lengthMenu: [5, 10, 20, 50, 100, 200, 500],
        columns: [
            {   data: "rate.ref_rate" },
            {   data: "rate.currency" },
            {   data: "rate.ref_rate_period" },
            {   data: "rate.date" },
            {   data: "rate.rate" },
            {   data: "rate.update_time" }
        ],
        order: [[ 3, 'desc' ]],
        select: {
            style: 'single'
        },            
        buttons: [
            {   extend: "create", editor: rateEditor },
            {   extend: "edit",   editor: rateEditor },
            {   extend: "remove", editor: rateEditor },
                        "colvis"
        ]
    } );
}

If you don't want to edit the table, fine. Makes it even easier; like this:

function tblRate() {
    var rateTable = $('#tblRate').DataTable( {
        dom: "Bfrltip",
        processing: true,
        serverSide: true,    //server side only works well with type "POST" !!!
        ajax: {
            url: 'actions.php?action=tblRate',
            type: 'POST'
        },
        pageLength: 20,
        lengthMenu: [5, 10, 20, 50, 100, 200, 500],
        columns: [
            {   data: "rate.ref_rate" },
            {   data: "rate.currency" },
            {   data: "rate.ref_rate_period" },
            {   data: "rate.date" },
            {   data: "rate.rate" },
            {   data: "rate.update_time" }
        ],
        order: [[ 3, 'desc' ]],
        buttons: [
                        "colvis"
        ]
    } );
}

And on the server side you have this (PHP):

function tblRate(&$db, &$lang) {    
    Editor::inst( $db, 'rate' )
        ->field(
            Field::inst( 'rate.ref_rate' ),
            Field::inst( 'rate.currency' ),
            Field::inst( 'rate.ref_rate_period' ),
            Field::inst( 'rate.date' )
                ->getFormatter( function ( $val, $data, $opts ) {
                        return getFormatterDate($val);                     
                    } )
                ->setFormatter( function ( $val, $data, $opts ) {
                        return setFormatterDate($val);
                    } ),
            Field::inst( 'rate.rate' )
                ->validator( function ( $val, $data, $opts ) {
                    return validatorRate($data['rate'], $val);
                } )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterRate($val);
                })
                ->setFormatter( function($val, $data, $opts) {
                    return setFormatterRate($val);
                }),
            Field::inst( 'rate.update_time' )
        )
        ->process($_POST)
        ->json();
}

Again: If you don't want to edit the table it is even simpler:

function tblRate(&$db, &$lang) {    
    Editor::inst( $db, 'rate' )
        ->field(
            Field::inst( 'rate.ref_rate' ),
            Field::inst( 'rate.currency' ),
            Field::inst( 'rate.ref_rate_period' ),
            Field::inst( 'rate.date' )
                ->getFormatter( function ( $val, $data, $opts ) {
                        return getFormatterDate($val);                     
                    } ),
            Field::inst( 'rate.rate' )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterRate($val);
                }),
            Field::inst( 'rate.update_time' )
        )
        ->process($_POST)
        ->json();
}

silens

'''

[code]
El problema debe de ser el origen de los datos, esta funcion trabaja bien

$(document).ready(function() {
$('#tbl_entidad').DataTable({
ajax: "php/data.php",

    columns: [
     {data : "name"},
  ]
});

});

Esto es el data.php:

$dato = array(
"data"=>array(
array(
"name"=>"Peter",
"lastname"=>"Griffin",
"city"=>"Quahog",
"gender"=>"male"
)

    )

);

$data=json_encode($dato);
echo $data;

Aqui, data.php devuelve un array en php. Esto funciona bien, el problema es que mi pagina identidades.php, toma los datos de una web externa que los devuelve en formato json.

Esto seria identidades.php

$url="http://..........mi url" ;
$json = file_get_contents($url);
echo $json;

Este el el json que devuelve url

{
"count": 1000,
"total_count": 11667,
"ent": [
{
"id": 1,
"name": "fsadfsdfsdfsdfs.",
"pai": 1,
"cif": "324",
"fch": "2014-02-12T00:00:00.000Z"
}
}

Si uso la web data.php. Funcion bien, pero con identidades.php no.

No consigo entender cual es el problema

[/code]'''.)

silens

Creo que mi problema es igual que este:
https://datatables.net/forums/discussion/42374

rf1234

Can you please post your code using Markdown as described below. You only need to use the three back ticks and your code is legible so much better.

It really seems like you have the same issue. You will need to transform your JSON data into the format Data Tables expects! The JSON the URL gives you does not have the correct format.

Just use this to tranform the data:
https://datatables.net/reference/option/ajax.dataSrc

Search for "Manipulate the data returned from the server" and you'll find an example. Buena suerte!!

If you look at the JSON response below you can see that it does not comply with what Data Tables expects. Please also look at the ajax examples: https://datatables.net/examples/ajax/

Este el el json que devuelve url
{
"count": 1000,
"total_count": 11667,
"ent": [
{
"id": 1,
"name": "fsadfsdfsdfsdfs.",
"pai": 1,
"cif": "324",
"fch": "2014-02-12T00:00:00.000Z"
}
}

silens

Muchisimas gracias.
Yo necesito acceder a ent.id, ent.name, etc , count y total_count no vale...
Creo que tambien tengo que usar datasource=""

$(document).ready(function() {
    $('#tbl_entidad').DataTable( {
        "ajax": {
            "url": "php/identidades.php",
            "dataSrc": ""
        },
        "columns": [
            { "data": "ent[, ]" }
           
           
        ]
    } );
} );

Estoy buscando para ver si encuentro como hacerlo.
Muchas gracias por su insistencia.

rf1234

You can also transform the data on the server side with PHP and then return precisely what Data Tables expects. Puede ser la solución más fácil.

rf1234

Otra posibilidad: In your PHP script you read the data from the external site, insert it into an SQL database and subsequently use Editor and Data Tables to display and edit the data, as per my example above. I do that a lot with data that I get from screen scraping. Works fine!

silens

Ya lo he solucionado, he formateado la entrada de datos para dejar solo los que me interesaban. con el file_get_contents.
Al mandar el json directamente bien formateado, la funcion es realmente facil.

Muchisimas gracias por todo.

rf1234

De nada. I guess I also learned something. So far I haven't used data tables without Editor but I might do that for the reporting I am getting started with right now ...