how to have json reponse in client server (mysql) datatable use

juju24

i have this error
{"draw":1,"recordsTotal":5,"recordsFiltered":5,"data":[null,null,null,null,null]}

2
TypeError: data is null

return data[mSource];

and this is my script
html

id	nom	prenom	grade	organisme	plus de details

javascript

            $('#myModaltab').modal('hide');

            table=$('#users').DataTable({
                "columns": [

                    {"data": "id",
                        "visible":false,
                        "searchable": false


                    },

                    {"data": "nom",
                        "searchable": true},
                    {"data": "prenom",
                        "searchable": true},
                    {"data": "grade",
                        "searchable": true},
                    {"data": "organisme",
                        "searchable": true},
                    {
                        "targets": -1,
                        "data": null,
                        "searchable": false,
                        "orderable":false,

                        "defaultContent": "<button type='button' class='btn btn-info btn-md u3'  ><span class='glyphicon glyphicon-plus-sign'></button>"
                    },

                ],

                keys: true,
                "processing": true,
                "serverSide": true,
                "stateSave": true,

                "ajax": {
                    url: 'page.php',
                    type: 'POST',


                }
                ,


                scrollX:        true,

                language: {
                    processing:     "Traitement en cours...",
                    search:         "Rechercher&nbsp;:",
                    lengthMenu:    "Afficher _MENU_ &eacute;l&eacute;ments",
                    info:           "Affichage de l'&eacute;lement _START_ &agrave; _END_ sur _TOTAL_ &eacute;l&eacute;ments",
                    infoEmpty:      "Affichage de l'&eacute;lement 0 &agrave; 0 sur 0 &eacute;l&eacute;ments",
                    infoFiltered:   "(filtr&eacute; de _MAX_ &eacute;l&eacute;ments au total)",
                    infoPostFix:    "",
                    loadingRecords: "Chargement en cours...",
                    zeroRecords:    "Aucun &eacute;l&eacute;ment &agrave; afficher",
                    emptyTable:     "Aucune donnée disponible dans le tableau",
                    paginate: {
                        first:      "Premier",
                        previous:   "Pr&eacute;c&eacute;dent",
                        next:       "Suivant",
                        last:       "Dernier"
                    },
                    aria: {
                        sortAscending:  ": activer pour trier la colonne par ordre croissant",
                        sortDescending: ": activer pour trier la colonne par ordre décroissant"
                    }
                }
            });

php

define("HOST", "localhost");
define("USER", "root");
define("PASSWORD", "");
define("DB", "bdexepert");
define("MyTable", "expert1");

$connection = mysqli_connect(HOST, USER, PASSWORD, DB) OR DIE("Impossible to access to DB : " . mysqli_connect_error());

$primaryKey = 'id';
/* END DB Config and connection */

/*
 * @param (string) SQL Query
 * @return multidim array containing data array(array('column1'=>value2,'column2'=>value2...))
 *
 */
function getData($sql){
    global $connection ;//we use connection already opened
    $query = mysqli_query($connection, $sql) OR DIE ("Can't get Data from DB , check your SQL Query " );
    $data = array();
    foreach ($query as $row ) {
        $data[] = $row ;

    }
    return $data;
}

/* Useful $_POST Variables coming from the plugin */
$draw = $_POST["draw"];//counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
$orderByColumnIndex  = $_POST['order'][0]['column'];// index of the sorting column (0 index based - i.e. 0 is the first record)
$orderBy = $_POST['columns'][$orderByColumnIndex]['data'];//Get name of the sorting column from its index
$orderType = $_POST['order'][0]['dir']; // ASC or DESC
$start  = $_POST["start"];//Paging first record indicator.
$length = $_POST['length'];//Number of records that the table can display in the current draw
/* END of POST variables */

$recordsTotal = count(getData("SELECT * FROM expert1 where activstate=".$cle));

/* SEARCH CASE : Filtered data */
if(!empty($_POST['search']['value'])){


    /* WHERE Clause for searching */
    for($i=0 ; $i<count($_POST['columns'])-1;$i++){
        $column = $_POST['columns'][$i]['data'];//we get the name of each column using its index from POST request
        $where[]="$column like '%".$_POST['search']['value']."%'";
    }
    $where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....
    /* End WHERE */

    $sql = sprintf("SELECT * FROM %s %s and activstate=%s", MyTable , $where,$cle);//Search query without limit clause (No pagination)

    $recordsFiltered = count(getData($sql));//Count of search result

    /* SQL Query for search with limit and orderBy clauses*/
    $sql = sprintf("SELECT * FROM %s %s and activstate=%s  ORDER BY %s %s limit %d , %d ", MyTable , $where ,$cle ,$orderBy, $orderType ,$start,$length );
    //echo $sql;
    $data = getData($sql);
    /* END SEARCH */
}else {

    $sql = sprintf("SELECT * FROM %s where activstate=%s  ORDER BY %s %s limit %d , %d ", MyTable,$cle ,$orderBy,$orderType ,$start , $length);

// echo $sql ;
$data = getData($sql);

    $recordsFiltered = $recordsTotal;

}

/* Response to client before JSON encoding */
$response=array(
    "draw"=>intval($draw),
    "recordsTotal" =>$recordsTotal,
    "recordsFiltered"=>$recordsFiltered,
    "data"=>$data
);

// print_r($data);

//echo json_encode($data);
$response=utf8_converter($response);
echo json_encode($response, JSON_UNESCAPED_UNICODE);

} else {
echo "NO POST Query from DataTable";
}

function utf8_converter($array)
{
array_walk_recursive($array, function(&$item, $key){
if(!mb_detect_encoding($item, 'utf-8', true)){
$item = utf8_encode($item);
}
});

return $array;

}

juju24

i don't find the error need your help

allan

It sounds like an issue in your PHP code. I could read through it under the support options, but I'd recommend you just add some debugging statements to it to understand where the error is coming from.

Allan