来自服务器端处理的jquery数据表的空值

Question

I am trying to populate jquery datatable with server side processing. In php I used mysql join query to get data from two tables.

My query looks like this:

$sQuery = "SELECT SQL_CALC_FOUND_ROWS u.user_id 
                    , CONCAT(u.first_name, ' ', u.last_name)
                    , u.gender
                    , CONCAT(a.suburb, ', ', a.zip_code)
                    , u.date_registered
            FROM users u 
            INNER JOIN user_addresses a ON a.user_id = u.user_id AND a.address_type = 1 ".$sWhere.$sOrder.$sLimit;

And columns array is something like this:

$aColumns = array (
                    "u.user_id", 
                    "CONCAT(u.first_name, ' ', u.last_name)", 
                    "u.gender", 
                    "CONCAT(a.suburb, ', ', a.zip_code)", 
                    "u.date_registered"
                  );

My problem is its display data only for 2nd and 4th columns. Its gave null values for other columns.

{
 "sEcho": 1,
 "iTotalRecords": "7",
 "iTotalDisplayRecords": "7",
 "aaData": [
     [null, "Tharanga Kumara", null, "Wanniassa
, 2903", null],
     [null, "dsfadsf fdaf", null, "Ashford, 5035", null],
     [null, "fdafa dfdaf", null, "Wanniassa, 2903", null],
     [null, "Dilani Kumari", null, "dfdaf, 34234", null],
     [null, "Kamal Maduranga ", null, "Wanniassa, 2903", null],
     [null, "Sampath Gunathilaka", null, "Eastern Suburbs Mc, 2004", null],
     [null, "Kasun Sandaruwan", null, "Wanniassa, 2903", null]
 ]

}

UPDATE: datatable code:

.DataTable({
  bAutoWidth: false,    
  "bProcessing": true,
  "bServerSide": true,
  "sAjaxSource": "includes/process_customer_datatable.php",

  "aoColumnDefs":  [{ 
                      "aTargets": [0],
                      "bSortable": false,
                      //"sTitle": "ip", 
                      "sClass": "center",
                      "mRender": function (data, type, full) {
                         return '<label class="pos-rel">' + 
                                  '<input class="custom" type="checkbox">' + 
                                '</label>';
                      }
                    },
                    {
                     "aTargets": [5],    //Delete column
                     "mData": "0",  
                     "mRender": function (data, type, full) {
                       return '<a  href=' + 'index.php?customer=' + data + '>Delete</a>';
                     }                    
                    }
                  ],



  // "aoColumns": [
  //   { "bSortable": false },
  //   null, 
  //   null,
  //   null,  
  //   null, 
  //   null, 
  //   { "bSortable": false }, 
  // ],
  "aaSorting": [],

});

Can anybody tell me what is problem of this? Hope somebody may help me out.

Answer 1

You can stringify the object returned from your PHP. Replace the null values using regex with a blank string then parse the string back to a JSON object.

Example here:

http://jsfiddle.net/yhoLboLr/

var data = {
    "sEcho": 1,
    "iTotalRecords": "7",
    "iTotalDisplayRecords": "7",
    "aaData": [
        [null, "Tharanga Kumara", null, "Wanniassa, 2903", null],
        [null, "dsfadsf fdaf", null, "Ashford, 5035", null],
        [null, "fdafa dfdaf", null, "Wanniassa, 2903", null],
        [null, "Dilani Kumari", null, "dfdaf, 34234", null],
        [null, "Kamal Maduranga ", null, "Wanniassa, 2903", null],
        [null, "Sampath Gunathilaka", null, "Eastern Suburbs Mc, 2004", null],
        [null, "Kasun Sandaruwan", null, "Wanniassa, 2903", null]
    ]
};

var cleanData = JSON.parse(JSON.stringify(data).replace(/null/g, "\"\""));
console.log(cleanData);