Struggling to get DataTables working with Ajax / JSON / PHP

Bollard

Hi All,

Would really appreciate some help as im starting to tear my hair out here!

The issue is the data is not being filled into the table despite a JSON object being returned correctly from my PHP - so i assume there is something wrong / missing in my JS. I also get "b is undefined" (on line 68 or jquery.datatables.min.js) error when i check the console in FireBug. Here is the code:

JS:
[code]$("form#ArtifactSearchForm").submit(function() {

$('#SearchResults').dataTable({
"bProcessing": true,
"bServerSide": true,
"bRetrieve": true,
"sAjaxSource": "process.php?action=searchArtifact",
"fnServerData": function (sSource, aoData, fnCallback){
aoData.push({
"name": "searchName",
"value": $('#ArtifactSearch').attr('value')
});
$.ajax({
"dataType": "json",
"type": "POST",
"url": sSource,
"data": aoData,
success: function(data) { fnCallback(data); }
});
}
});
});[/code]

HTML:
[code]



Artifact ID
Artifact Name
Artifact Type
Artifact Location
Artifact Domain
Artifact Author
Artifact Label
Registered Email
Registered Date
Registered Time





[/code]

PHP:
[code]
// Perform the artifact search
if(isset($_GET['action']) && $_GET['action'] == 'searchArtifact'){
include(realpath($_SERVER["DOCUMENT_ROOT"]).'/includes/dbLogin.php');
$connection = oci_connect($db_user, $db_password, $db_connect);

$query = "SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LABEL, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, REGISTERED_DATE, REGISTERED_TIME FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['searchName']."%'";
$statement = oci_parse($connection, $query);
oci_execute($statement);
$rows = array();

while($row = oci_fetch_assoc($statement)) {
$rows[] = $row;
}

oci_free_statement($statement);
oci_close($connection);

$response = json_encode($rows, JSON_FORCE_OBJECT);
die($response);
}
[/code]

JSON:
[code]
{"0":{"ARTIFACT_ID":"4E2FE3BCE356C","ARTIFACT_NAME":"123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_123","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:09:00"},"1":{"ARTIFACT_ID":"4E2D9D2E7B2C1","ARTIFACT_NAME":"getTEST123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Ext_XYZ_getTEST123","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"XYZ","ARTIFACT_AUTHOR":"RB","REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"25-07-2011","REGISTERED_TIME":"17:43:26"},"2":{"ARTIFACT_ID":"4E2FE55411E2F","ARTIFACT_NAME":"abc123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_abc123","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:15:48"},"3":{"ARTIFACT_ID":"4E32A7F9545EF","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Ext_GTO_123456","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"GTO","ARTIFACT_AUTHOR":"Test","REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:30:49"},"4":{"ARTIFACT_ID":"4E32A89765E45","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_123456","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":"e.g. Joe Bloggs","REGISTERED_EMAIL":"test123@test.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:33:27"}}
[/code]

I would really appreciate any help - im sure its a tiny issue but having never used DataTables before i dont know where to look

Thank you

fbas

it's not enough for your JSON to be valid JSON, it also has to be an object that DataTables expects and wants.

see the right hand side of this page: http://www.datatables.net/usage/server-side

in the simplest/default case, your JSON should be an object that contains the following:
iTotalRecords
iTotalDisplayRecords
sEcho
sColumns
aaData

what you are returning is an object of your $rows, looking like:
0
1
2
etc.

you can use Allan's PHP server_processing.php code as a starting point:
http://www.datatables.net/release-datatables/examples/data_sources/server_side.html

particularly look at the "OUTPUT" section at the end. this is where he creates the object to return as JSON and attaches the $rows as the aaData array

Bollard

Thank you for your response.

I have made the changes as required to my PHP:

[code]if(isset($_GET['action']) && $_GET['action'] == 'searchArtifact'){
include(realpath($_SERVER["DOCUMENT_ROOT"]).'/includes/dbLogin.php');
$connection = oci_connect($db_user, $db_password, $db_connect);

// Total data set length
$countQuery = "SELECT COUNT (ARTIFACT_ID) AS NUM_ROWS_TEST FROM PROCESS_OWNER.ARTIFACTS";
$countStatement = oci_parse($connection, $countQuery);
oci_define_by_name($countStatement, 'NUM_ROWS_TEST', $iTotalRecords);
oci_execute($countStatement);
oci_fetch($countStatement);

//Filtered data length
$filteredQuery = "SELECT COUNT (ARTIFACT_ID) AS NUM_ROWS_FILTERED FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['searchName']."%'";
$filteredStatement = oci_parse($connection, $filteredQuery);
oci_define_by_name($filteredStatement, 'NUM_ROWS_FILTERED', $iTotalDisplayRecords);
oci_execute($filteredStatement);
oci_fetch($filteredStatement);

// Search the DB
$query = "SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LABEL, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, REGISTERED_DATE, REGISTERED_TIME FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['searchName']."%'";
$statement = oci_parse($connection, $query);
oci_execute($statement);

$rows = array();
while($row = oci_fetch_assoc($statement)) {
$rows[] = $row;
}

oci_free_statement($statement);
oci_close($connection);

$output = array(
"sEcho" => intval($_POST['sEcho']),
"iTotalRecords" => $iTotalRecords,
"iTotalDisplayRecords" => $iTotalDisplayRecords,
"aaData" => $rows
);

//echo var_dump($rows);
$response = json_encode($output, JSON_FORCE_OBJECT);
die($response);

}
[/code]

I am now returning:
[code]
{"sEcho":1,"iTotalRecords":"164","iTotalDisplayRecords":"5","aaData":{"0":{"ARTIFACT_ID":"4E2FE3BCE356C","ARTIFACT_NAME":"123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_123","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:09:00"},"1":{"ARTIFACT_ID":"4E2D9D2E7B2C1","ARTIFACT_NAME":"getTEST123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Ext_XYZ_getTEST123","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"XYZ","ARTIFACT_AUTHOR":"RB","REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"25-07-2011","REGISTERED_TIME":"17:43:26"},"2":{"ARTIFACT_ID":"4E2FE55411E2F","ARTIFACT_NAME":"abc123","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_abc123","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:15:48"},"3":{"ARTIFACT_ID":"4E32A7F9545EF","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Ext_GTO_123456","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"GTO","ARTIFACT_AUTHOR":"test","REGISTERED_EMAIL":"test@test.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:30:49"},"4":{"ARTIFACT_ID":"4E32A89765E45","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LABEL":"DB_Int_EAS_123456","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":"e.g. Joe Bloggs","REGISTERED_EMAIL":"test123@test.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:33:27"}}}
[/code]

But still the table is empty ("No matching records found") despite below that "Showing 1 to 5 of 5 entries (filtered from 164 total entries)"

Any ideas?!

fbas

aaData must be an array, you are returning an object (assoc array is mapped to object in the json_encode).

aaData is traditionally an array of array, but you can return array of object. I think you'll need to use mDataProp if that's the case.

I'm assuming the oci interface allows you to fetch as an array rather than associative array, which will probably do the trick:

[code]
while($row = oci_fetch_array($statement)) {
$rows[] = $row;
}

[/code]

Bollard

EDIT: Got it "working" (ie data is now being filled) by using mDataProp - great. However, none of the features are working.

For instance, DataTables says "Showing 1 to 10 of 49 entries (filtered from 164 total entries)" but it displays all 49.

I also tried to use:

[code]
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [
"copy",
"csv",
"xls",
"print"
]
}

[/code]

But nothing shows?

This is starting to drive me mad!

__________________________________________________________________________________________
Thank you for your continuted help, fbas.

[code]oci_fetch_array[/code]

Does not return what i want, i have instead removed JSON_FORCE_OBJECT, and now return the following JSON:

[code]
{"sEcho":1,"iTotalRecords":"164","iTotalDisplayRecords":"5","aaData":[{"ARTIFACT_ID":"4E2FE3BCE356C","ARTIFACT_NAME":"123","ARTIFACT_TYPE":"UI","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"ARTIFACT_LABEL":"DB_Int_EAS_123","REGISTERED_EMAIL":"test@db.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:09:00"},{"ARTIFACT_ID":"4E2D9D2E7B2C1","ARTIFACT_NAME":"getTEST123","ARTIFACT_TYPE":"UI","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"XYZ","ARTIFACT_AUTHOR":"RB","ARTIFACT_LABEL":"DB_Ext_XYZ_getTEST123","REGISTERED_EMAIL":"test@db.com","REGISTERED_DATE":"25-07-2011","REGISTERED_TIME":"17:43:26"},{"ARTIFACT_ID":"4E2FE55411E2F","ARTIFACT_NAME":"abc123","ARTIFACT_TYPE":"UI","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":null,"ARTIFACT_LABEL":"DB_Int_EAS_abc123","REGISTERED_EMAIL":"test@db.com","REGISTERED_DATE":"27-07-2011","REGISTERED_TIME":"11:15:48"},{"ARTIFACT_ID":"4E32A7F9545EF","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LOCATION":"Ext","ARTIFACT_DOMAIN":"GTO","ARTIFACT_AUTHOR":"Ryan Ballard","ARTIFACT_LABEL":"DB_Ext_GTO_123456","REGISTERED_EMAIL":"test@db.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:30:49"},{"ARTIFACT_ID":"4E32A89765E45","ARTIFACT_NAME":"123456","ARTIFACT_TYPE":"UI","ARTIFACT_LOCATION":"Int","ARTIFACT_DOMAIN":"EAS","ARTIFACT_AUTHOR":"e.g. Joe Bloggs","ARTIFACT_LABEL":"DB_Int_EAS_123456","REGISTERED_EMAIL":"test123@db.com","REGISTERED_DATE":"29-07-2011","REGISTERED_TIME":"13:33:27"}]}
[/code]

If you put this into jsonlint.com you will see it is in EXACTLY the same format as http://www.datatables.net/usage/server-side - ie i am returning an array of objects.

However, data is till not filled (i am now getting blank td's) and the error message:

[code]
DataTables warning (table id = 'SearchResults'): Requested unknown parameter '0' from the data source for row 0
[/code]

Ideas?

Thank you for your help

fbas

yes, the json looks good. but does DataTables have a problem with null? aaData[0]["ARTIFACT_AUTHOR"] is null. try setting sDefaultContent: "" for your columns to see if null might be the issue. (or don't allow null in the DB or catch it in your PHP)

http://www.datatables.net/ref#sDefaultContent

I recall there was an issue the other day in the code where null was not being detected properly in 1.8.1 and thus crashing someone else's function. sDefaultContent is a valid workaround, and Allan plugged up the source for the next release.

fbas

to get oci_fetch_array to only return a numeric array, give it a $mode = OCI_NUM. else it returns an array with both associative keys and integer keys.

array oci_fetch_array ( $statement, OCI_NUM )

--EDIT--
or simply call oci_fetch_row ( $statement ). same thing.

Bollard

Thanks for all your help fbas

I tried the sDefaultContent: "" setting on all my columns, but am still facing the same error where DataTables says "Showing 1 to 10 of 49 entries (filtered from 164 total entries)" but it displays all 49.

I also tried DataTables 1.8.2.dev and 1.8.0 and still get the same issue.

Any ideas?

allan

One thing that looks odd about this:

[code]
"sEcho":1,"iTotalRecords":"164","iTotalDisplayRecords":"5"
[/code]

is that iTotalRecords and iTotalDisplayRecords should be integers, not strings. DataTables can do some slightly odd things when you give it strings there.

Also, unless you have a filter applied, iTotalRecords and iTotalDisplayRecords should be the same ( http://datatables.net/usage/server-side ).

Allan

fbas

add a LIMIT clause to you SQL

your original query:
[code]
$query = "SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LABEL, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, REGISTERED_DATE, REGISTERED_TIME FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['searchName']."%'";
[/code]

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

[code]
$query = "SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LABEL, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, REGISTERED_DATE, REGISTERED_TIME FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['searchName']."%' " . $sLimit; // <--- add $sLimit
[/code]

Bollard

Thank you for the responses.

I have used [code]intval[/code] to get [code]iTotalRecords[/code] and [code]iTotalDisplayRecords[/code] as integers.

I am confused about them being the same length however - i thought filtering was doing the search? Total dataset (ie all records in the table) is 164. My search for "abc" returns 5 results?

I have added the LIMIT (a bit of a pain in Oracle...) and can now get the number of results limited as desired. However, i would like to have pagination instead of a simple drop down, and am worried about [code]iTotalRecords[/code] and [code]iTotalDisplayRecords[/code] not matching. My updated code is:

[code]
if(isset($_GET['action']) && $_GET['action'] == 'searchArtifact'){
include(realpath($_SERVER["DOCUMENT_ROOT"]).'/includes/dbLogin.php');
$connection = oci_connect($db_user, $db_password, $db_connect);

// Total number of rows in the table
$countQuery = "SELECT COUNT (ARTIFACT_ID) AS ROWS_TOTAL FROM PROCESS_OWNER.ARTIFACTS";
$countStatement = oci_parse($connection, $countQuery);
oci_define_by_name($countStatement, 'ROWS_TOTAL', $iTotalRecords);
oci_execute($countStatement);
oci_fetch($countStatement);

// Number of rows we are actually looking for
$filterQuery = "SELECT COUNT (ARTIFACT_ID) AS ROWS_FILTERED FROM PROCESS_OWNER.ARTIFACTS WHERE ARTIFACT_NAME LIKE '%".$_POST['ArtifactLabel']."%'";
$filterStatement = oci_parse($connection, $filterQuery);
oci_define_by_name($filterStatement, 'ROWS_FILTERED', $iTotalDisplayRecords);
oci_execute($filterStatement);
oci_fetch($filterStatement);

// Paging
if(isset($_POST['iDisplayStart']) && $_POST['iDisplayLength'] != '-1'){
$query = "
SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, to_char(REGISTERED_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS')
FROM PROCESS_OWNER.ARTIFACTS
WHERE ARTIFACT_NAME LIKE '%".$_POST['ArtifactLabel']."%'
ORDER BY REGISTERED_TIMESTAMP ASC
)
a WHERE ROWNUM <= ".$_POST['iDisplayLength']."
)
WHERE rnum >= ".$_POST['iDisplayStart']."
";
//print_r($sLimit);
} else {
// Search for what we are looking for
$query = "
SELECT ARTIFACT_ID, ARTIFACT_NAME, ARTIFACT_TYPE, ARTIFACT_LOCATION, ARTIFACT_DOMAIN, ARTIFACT_AUTHOR, ARTIFACT_LABEL, REGISTERED_EMAIL, to_char(REGISTERED_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS')
FROM PROCESS_OWNER.ARTIFACTS
WHERE ARTIFACT_NAME LIKE '%".$_POST['ArtifactLabel']."%'
";
}

$statement = oci_parse($connection, $query);
oci_execute($statement);

// Store results row by row
$results = array();
while($row = oci_fetch_row($statement)) {
$results[] = $row;
}

// Prepare the response for DataTables
$response = json_encode(array(
'sEcho' => intval($_POST['sEcho']),
'iTotalRecords' => intval($iTotalRecords),
'iTotalDisplayRecords' => intval($iTotalDisplayRecords),
'aaData' => $results
));

// Cleanup database connections
oci_free_statement($countStatement);
oci_free_statement($filterStatement);
oci_free_statement($statement);
oci_close($connection);

die($response);
}
[/code]

Thank you for all your help

allan

[quote]Bollard said: My search for "abc" returns 5 results?[/quote]

My mistake, I missed the fact that there was in fact a filer applied :-). When there is a filter applied then they can be different, when there is no filter they should be the same.

Allan

Bollard

Thank you for the clarification. So i dont need to change anything on the server side then?

I want to have the pagination effect (as can be seen here: http://datatables.net/release-datatables/examples/data_sources/server_side.html) but dont know how to achieve it? It doesnt seem to be documented and certainly doesnt seem to be achieved by simply:

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../server_side/scripts/server_processing.php"
} );
} );
[/code]

Thanks for the help