DataTables server-side processing:DataTable warning:JSON data from server could not be parsed.

rijz

This is my table in table.html
[code]




Contact_Name
Job_Title
Email
Phone







6 entries found






































[/code]

This is the javascript on the same table.html page

[code]

$(document).ready(function () {
$('#sorting-advanced').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../js/libs/DataTables/datatable.php"
});
});

[/code]



And this is the datatable.php
[code]

<?php

$aColumns = array( 'Contact_Name', 'Job_Title', 'Email', 'Phone');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "Contact_ID";

/* DB table to use */
$sTable = "mytable";

/* Database connection information */
$gaSql['user'] = "*********";
$gaSql['password'] = "*********";
$gaSql['db'] = "*********";
$gaSql['server'] = "*********.com";

/*
* MySQL connection
*/
$gaSql['link'] = mssql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mssql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".$_GET['iDisplayStart'] .", ".
$_GET['iDisplayLength'] ;
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mssql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]


But when i run the code it is showing error as

[quote]
DataTable warning(table id='sorting-advanced');
DataTable warning:JSON data from server could not be parsed.This is caused by a JSON formating error.

[/quote]

Can any one please let me know why is it showing the error. Or is there something am missing. Am very new to JSON. Please help me in sorting the issue.

allan

See http://datatables.net/faqs#json

Allan

rijz

Thanks Allan for the reply. Again as am novice to JSON, please guide me. How do i generate the JSON return to test JSON Lint. Please let me know the procedure to generate the JSON return and test it in JSON Lint.

allan

You use Firebug or Inspector to get the JSON that is being returned rom your server. Google for Firebug tutorials.

Allan

rijz

Problem solved.. The problem was i edited the Mysql query with mssql query. That made the error. Now when i tried to connect with the mysql database. This worked smoothly.
Now my question is how to connect to mssql database. Here when i tried to change mysql statement to mssql
[quote] Limit [/quote] to [quote] Top [/quote] there is a problem with the pagination. Is there any way to connect the Mssql database.

rijz

Is there any one who can help me in connecting the mssql database with the Datatable and still maintain the functionality like pagination, etc..

allan

I don't know about MSSQL myself, I'd suggest asking on SO or somewhere similar if you don't know how to create the query yourself.

Allan

rijz

I have used the datatable and select list. You can see the image link... http://pms.leadsys.co/Untitled%2063.jpg.

Is it possible to search the datatable based on the select list eg:Industry, Employee etc... and how ??
Please let me know is there a way to use datatable the way it is shown in the image.

Thanks in advance.

Regards
Rijesh

allan

Use fnFilter .

Allan

rijz

Thanks for the early reply. That is working fine for single test string. But how do we send multiple test string.

This is single test string.
[code]

$(document).ready(function () {

var oTable= $('#sorting-advanced').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./js/libs/DataTables/datatable.php",

});
oTable.fnFilter('Information Technology Manager');
[/code]


Can we use something like this
[code]
oTable.fnFilter('USA', 'Information Technology Manager');
[/code]

Please help!!
Regard
Rijesh

allan

http://datatables.net/api#fnFilter

You can use it to do a global filter, or a column filter. See the documentation for how to do each.

Allan

rijz

I went through the documentation, i came across fnMultifilter http://datatables.net/plug-ins/api#fnMultiFilter. Which i tried to use ,to solve the previous comment..


This is the select list... designation and the country
[code]

Managment
Engineering
Finance
HR
Legal
Marketing
Operations
Sales



USA
CANADA
UNITED KINGDOM
GERMANY
FRANCE
AUSTRALIA
NORWAY
SWEDEN

[/code]

this is the ajax...
[code]

$('select').change(function () {


var designation = $("#designation").val();
var revenue = $("#revenue").val();
var employees = $("#employees").val();
var industry = $("#industry").val();
var country = $("#country").val();



var oTable= $('#sorting-advanced').dataTable({
"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"bRetrieve": true,
"sAjaxSource": "./js/libs/DataTables/buildlist.php",
"aoColumns": [
{ "sName": "Contact_Name" },
{ "sName": "Job_Title" },
{ "sName": "Email" },
{ "sName": "Phone" },
{ "sName": "Revenue" },
{ "sName": "Employees" },
{ "sName": "Industry" },
{ "sName": "Country" }
]

});
//oTable.fnFilter('Information Technology Manager');

oTable.fnMultiFilter({ "Job_Title": designation, "Revenue": revenue, "Employees": employees,"Country": country, "Industry": industry });


});
[/code]


But again this is not working for multiple selections on select list . Will it work with fnMultifilter??
What am i missing.
Please help, and thank you very much for your kind support Allan.

Regards,
Rijesh

allan

Multi filter just calls fnFilter - so if you can't solve the issue with fnFilter, the multi filter won't help. Have you implemented column filtering in your server-side processing script?

Allan

rijz

Thanks again Allan.
This is the server side script i am using..

[code]
<?php

$aColumns = array( 'Contact_Name', 'Job_Title', 'Email', 'Phone','Revenue','Employees','Industry','Country');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "Contact_ID";

/* DB table to use */
$sTable = "mytable";

/* Database connection information */
$gaSql['user'] = "*********";
$gaSql['password'] = "*********";
$gaSql['db'] = "*********";
$gaSql['server'] = "*********.com";

/*
* MySQL connection
*/
$gaSql['link'] = mssql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mssql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".$_GET['iDisplayStart'] .", ".
$_GET['iDisplayLength'] ;
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mssql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]
This is the script am using:
[code]

$('select').change(function () {


var designation = $("#designation").val();
var revenue = $("#revenue").val();
var employees = $("#employees").val();
var industry = $("#industry").val();
var country = $("#country").val();


var oTable = $('#sorting-advanced').dataTable({

"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"bRetrieve": true,
"sAjaxSource": "./js/libs/DataTables/buildlist.php",
"aoColumns": [
{ "sName": "Contact_Name" },
{ "sName": "Job_Title" },
{ "sName": "Email" },
{ "sName": "Phone" },
{ "sName": "Revenue" },
{ "sName": "Employees" },
{ "sName": "Industry" },
{ "sName": "Country" }
]

});
// oTable.fnFilter(search)

oTable.fnMultiFilter({ "Job_Title": designation, "Revenue": revenue, "Employees": employees, "Country": country, "Industry": industry });


});

[/code]

What is that am missing in the above code? Please let me know or give me a link to refer.

Regards,
Rijesh

rijz

Hello,

I almost went through whole of documentation. But i could not find anything where the same column is filtered with multiple keyword. Is it after all possible in datatable?

for example: the industry column need to be filtered using the select list , and the selection can be multiple. Like Retail and software. Based on that the datatable should show up.

I tried fnfilter and fnMultifilter , i got result using fnMultifilter but only for single selection of select list.
The image is here http://pms.leadsys.co/Untitled%2065.jpg

For multiple selection of select list it is not working.
Here is the image http://pms.leadsys.co/Untitled%2064.jpg

The php script and the jquery i used is pasted on the above post.


How should crack this? What is the way to sort this out. Please help!!!

Thanks,
Regards
Rijesh

allan

> But i could not find anything where the same column is filtered with multiple keyword. Is it after all possible in datatable?

Yes it is possible, but not it is not in DataTables. If you were using client-side processing then it would work automatically - however, as it is, you'll need to modify the scripts you posted above to do multi-word matching on column filters. Probably not a trivial change I'm afraid.

Allan

rijz

I'm just beginner in jquery and php. Can u show me an example to do this or a link?
Please

Regards,
Rijesh

allan

I don't have an example to hand for this I'm afraid. It'd probably take a couple of hours to put together.

Allan

rijz

I think some one should come up with solution for the problem and put it in example section. This will be very helpful for many of them out there. It will also enhance the feature of the datatable.

Would you be able to help me out from this ? I would really appreciate it.

Regards,
Rijesh

rijz

Finally i could solve the issue. It is working very fine. :)

Thanks Allan for your kind support.

Regards
Rijesh