DataTables warning: table id=dataTables - Ajax err

Question

I am trying to get data from a MySQL database through PHP & Ajax to be displayed in a table by using DataTables. I am using XAMPP 1.8.3

This is part of my html code:

<table id="dataTables-melate" class="table table-striped table-bordered table-hover" cellspacing="0" width="100%">
                                <thead>
                                    <tr>
                                        <th>Concurso</th>
                                        <th>R1</th>
                                        <th>R2</th>
                                        <th>R3</th>
                                        <th>R4</th>
                                        <th>R5</th>
                                        <th>R6</th>
                                    </tr>
                                </thead>

                                <tfoot>
                                    <tr>
                                        <th>Concurso</th>
                                        <th>R1</th>
                                        <th>R2</th>
                                        <th>R3</th>
                                        <th>R4</th>
                                        <th>R5</th>
                                        <th>R6</th>
                                    </tr>
                                </tfoot>
</table>

This is my php script (now edited and working):

    //default_chart_numbers.php
    $loteria='revancha';
    $lotto = new Lotto();

    $ultimos_resultados=$lotto->last_results($loteria,20);

    //echo json_encode($ultimos_resultados);
/*Formatting the output to a non associative array*/
function objectToArray($d) 
{
    if (is_object($d)) {
        // Gets the properties of the given object
        // with get_object_vars function
        $d = get_object_vars($d);
    }

    if (is_array($d)) {
        /*
        * Return array converted to object
        * Using __FUNCTION__ (Magic constant)
        * for recursive call
        */
        return array_map(__FUNCTION__, $d);
    } else {
        // Return array
        return $d;
    }
}

$new_array = objectToArray($ultimos_resultados);
//echo '<pre>',print_r($new_array),'</pre>';

$result = array();
echo '[';
foreach ($new_array as $new_array2) {
    echo '[';
    foreach ($new_array2 AS $value){
        echo $value;
        if($value!==end($new_array2)){ //referencias: http://stackoverflow.com/a/8780881/1883256
            echo',';
        }
    }
    echo ']';//referencias: http://www.mydigitallife.info/how-to-access-php-array-and-multidimensional-nested-arrays-code-syntax/
    if($new_array2!==end($new_array)){
        echo ',';
    }else{ echo '';}
}
echo ']';

This is how the output data of the PHP script looks like (now with the new change):

[[2738,11,12,28,30,50,54], ... ,[2757,32,34,35,36,50,55]]

And here is the jQuery code:

<script>
$(document).ready(function() {
    $('#dataTables-melate').dataTable({
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": {
            "url":"ajax/default_chart_numbers.php",
            "type": "POST"
        },
        "columns":[
            {"data": "concurso"},
            {"data": "R1"},
            {"data": "R2"},
            {"data": "R3"},
            {"data": "R4"},
            {"data": "R5"},
            {"data": "R6"}
        ]
    });
} );
</script>

When i load the page (in Firefox), I get this error: DataTables warning: table id=dataTables-melate - Ajax error.
Firebug tells there's this error as well: 404 Not Found

What am i missing? I've been struggling with this since so long :/

Answer 1

This answer is going to be a somewhat different approach to using AJAX with DataTables, and hopefully it will help some, because it's much less code.

When using AJAX and adding data to DataTables I usually go this route: 1) echo json_encode on the server side just like you are doing. 2) in the success method of my ajax call I would have this:

Where "column_data" is basically just an array of the data values that correspond to each column. DataTables automatically adds data this way by counting how many values are in this array and pushing each value (column data) to the row based on the index in the array. So basically you just need just to make sure the number of columns you have equals the size of this array, and also make sure that in this array, your data is in the correct order that you want it to be displayed.

$.ajax({
    url: "your_path",
    type: "post_or_get",
    success : function (resp){
        // would look something like ['val1','val2', 'etc']
        var column_data = $.parseJSON(resp);

        // adding data to datatables
        // if column_data is 1 row
        $('your_table_element').dataTable().fnAddData(column_data);

        // to add multiple rows (array of arrays, just loop)
        for (var j=0;j<=column_data.length-1;++j){
            // adding each row with its column data
            $('your_table_element').dataTable().fnAddData(column_data[j]);
        }
    },
    error: function(jqXHR, textStatus, ex) {
      console.log(textStatus + "," + ex + "," + jqXHR.responseText);
    }
});

So in PHP you don't really need the return data to be an associative array. This is how I'm currently implementing it and it works fine for me.

Note: A common error with this method is the length of the return data array not equaling the number of columns you have. So make sure they are equal. If they are not, you'll probably see an error from DataTables saying hinting that a column doesn't exist etc, etc.