Server side search error
Server side search error
I have implemented server side php-postgres script and it is working with the exception of the dynamic search field. Everytime I start to type something in the field, a message pops up giving me this error:
DataTables warning: JSON data from server failed to load or be parsed. This is most likely to be caused by a JSON formatting error.
Help please..
DataTables warning: JSON data from server failed to load or be parsed. This is most likely to be caused by a JSON formatting error.
Help please..
This discussion has been closed.
Replies
Allan
<?php
$aColumns = array( 'item_id', 'batch_id', 'client_id', 'debit_amt', 'item_status' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "item_id";
/* DB table to use */
$sTable = "item";
/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* DB connection
*/
$gaSql['link'] = pg_connect(
" host=".$gaSql['server'].
" dbname=".$gaSql['db'].
" user=".$gaSql['user'].
" password=".$gaSql['password']
) or die('Could not connect: ' . pg_last_error());
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".pg_escape_string( $_GET['iDisplayLength'] )." OFFSET ".
pg_escape_string( $_GET['iDisplayStart'] );
}
/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
Thanks,
Allan
{
"sEcho": 0,
"iTotalRecords": 21883,
"iTotalDisplayRecords": 21883,
"aaData": [
[
"168",
"1",
"1000000008",
"10",
"338.00"
],
[
"2362",
"1",
"1000000134",
"10",
"0.00"
],
[
"169",
"1",
"1000000008",
"10",
"332.00"
],
[
"170",
"1",
"1000000008",
"10",
"167.00"
],
[
"171",
"1",
"1000000008",
"10",
"382.00"
],
[
"172",
"1",
"1000000008",
"10",
"363.00"
],
[
"173",
"1",
"1000000008",
"10",
"325.00"
],
[
"2363",
"1",
"1000000134",
"10",
"0.00"
],
[
"2365",
"1",
"1000000134",
"10",
"0.00"
],
[
"166",
"1",
"1000000008",
"10",
"360.00"
]
]
}
<?php
/*
* Script: DataTables server-side script for PHP and PostgreSQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
*/
$aColumns = array( 'item_id', 'client_id', 'batch_id', 'item_status', 'debit_amt' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "item_id";
/* DB table to use */
$sTable = "item";
/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "";
/*
* DB connection
*/
$gaSql['link'] = pg_connect(
" host=".$gaSql['server'].
" dbname=".$gaSql['db'].
" user=".$gaSql['user'].
" password=".$gaSql['password']
) or die('Could not connect: ' . pg_last_error());
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".pg_escape_string( $_GET['iDisplayLength'] )." OFFSET ".
pg_escape_string( $_GET['iDisplayStart'] );
}
/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
DataTables example
@import "../../media/css/demo_page.css";
@import "../../media/css/demo_table.css";
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php"
} );
} );
DataTables server-side processing example
Preamble
Live example
Rendering engine
Browser
Platform(s)
Engine version
CSS grade
Loading data from server
Rendering engine
Browser
Platform(s)
Engine version
CSS grade
Please refer to the DataTables documentation for full information about its API properties and methods.
DataTables © Allan Jardine 2008-2010
"pg_escape_string"
ie. you wrote:
$sWhere .= $aColumns[$i]." ILIKE '%".pg_escape_string( $_GET['sSearch'] )."%' OR ";
try:
$sWhere .= $aColumns[$i]." ILIKE '%" . $_GET['sSearch'] . "%' OR ";
The json data you posted is it the data you obtained by calling the server-side script with a search keyword? i.e. by calling it with sSearch=somthing?
When I was using the example code for server-side I had a similar problem and it all has to do with the escape string.
You see, JSON was escaping my escape characters and therefore when it got to be time to process it it would fail.
So try this, remove all the (not sure if this will be necessary)
[code]
pg_escape_string()
[/code]
and get rid of (fairly sure this should help)
[code]
$sOutput .= '"'.str_replace('"', '\"', $aRow[ $aColumns[$i] ]).'",';
[/code]
and see if that will work.
Also Allan, your while statement in the example should look like this:
[code]
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
$sOutput .= '"'.$aRow['engine'].'",';
$sOutput .= '"'.$aRow['browser'].'",';
$sOutput .= '"'.$aRow['platform'].'",';
if ( $aRow['version'] == "0" )
$sOutput .= '"-",';
else
$sOutput .= '"'.$aRow['version'].'",';
$sOutput .= '"'.$aRow['grade'].'"';
$sOutput .= "],";
}
[/code]
I have tested this and it works for me.
Going back to javaprog's JSON output - I'd say the problem is almost certainly the sEcho: 0. It should never be zero, and the table won't render if it is. It should start at one, and increment by one on every draw. It needs to pass back the value that DataTables sends it as sEcho - which should be this line:
[code]
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
[/code]
javaprog can you give us a link to this example please? If not, then can you check that the sEcho value is going up by one for each draw when sending and receiving.
Allan
This is the exact problem I am having now and was wondering if there was ever a fix for this?
Thanks
I am using exaclty this, whenever I type anything in the search box, I get the JSON error message.
http://datatables.net/development/server-side/php_postgres
Allan
Seems that the basic config script doesnt take into account if you are using integers since its going to try to do a LIKE 'integer' which wont work. As soon as I took out the integer field everything worked fine.