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?