从php返回的json无法解析为jQuery dataTables

Question

I have a simple mysql database table with library books. I am using a php page to retrieve the list of books. This is what it returns:

php get_books.php

{"iTotalRecords":"1","aaData":[{"author":"Tim Powers","title":"The Anubis Gates","genre":"Fiction","publisher":null,"year":null,"location":"Bookshelf","notes":null}]}

In jQuery dataTables, I have:

<script >
    $(document).ready(function() {
    $('#books').DataTable({
        "bServerSide": true,
        "sAjaxSource": "./get_books.php"
    });
});
</script>

When I run the web page with that script I get an alert:

DataTables warning (table id = 'books'): DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.

I can't find what the formatting error is. How should the data be formatted.

Here is the php page that does the return of the JSON data:

<?php
    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $offset = ($page-1)*$rows;
    $result = array();

    include 'conn.php';

    $rs = mysql_query("select count(*) from books");
    $row = mysql_fetch_row($rs);
    $result["iTotalRecords"] = $row[0];
    $rs = mysql_query("select * from books limit $offset,$rows");

    $items = array();
    while($row = mysql_fetch_object($rs)){
        array_push($items, $row);
    }
    $result["aaData"] = $items;

    echo json_encode($result);

?>

What should the return look like and how do I produce it?

Answer 1

There are many problems both with your JS and PHP code.

If you have less than a few thousands records in books table I would recommend disabling server-side processing mode by removing "bServerSide": true to enable client-side processing mode.

JavaScript:

$(document).ready(function() {
    $('#books').DataTable({
        "ajax": "get_books.php",
        "columns": [
           { "data": "author" },
           { "data": "title" },
           { "data": "genre" },          
           { "data": "location" }         
        ]
    });
});

PHP:

<?php   
    include 'conn.php';

    $rs = mysql_query("select author, title, genre, location from books");

    $result = array();
    $items = array();
    while($row = mysql_fetch_object($rs)){
        array_push($items, $row);
    }
    $result["data"] = $items;

    header("Content-type: application/json");
    header("Cache-Control: no-cache, must-revalidate");

    echo json_encode($result);
?>

HTML

<table id="books" class="display tablesorter">
   <thead>
      <tr>
         <th>Author</th>
         <th>Title</th>
         <th>Genre</th>
         <th>Location</th>
      </tr>
   </thead>
</table> 

See this jsFiddle for code and demonstration.

If you have more than a few thousands records, you will gain more performance by using server-side processing mode. But in this case I recommend using ssp.class.php helper library that comes in jQuery DataTables distribution (see examples/server_side/scripts folder).