PHP json_encode help

blainekasten

Hi, I'm working on getting values from a MySQL database using PDO.
Currently this is my json_encoded output, any advice on how to get this into the data tables?

[{"user_user_id":265,"ticket_id":1467568,"short_desc":"Gastroenterology EMR Forms - Dr. Heiser","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1469185,"short_desc":"Orthopedic Surgery iEHR forms - Joey","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1486511,"short_desc":"Hand Surgery iEHR Forms - Dr. Birndorf","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1487593,"short_desc":"Chiro iEHR \/ Clipboard Forms - Dr. Lawrence","office_name":"Temp Medical, M.D"}]

Thanks!

allan

Use sAjaxDataProp set to an empty string and mData - http://datatables.net/blog/Extended_data_source_options_with_DataTables

Allan

blainekasten

Okay, so if I use

$.post("index.php", {action: 'table_data', user_id: userID}, function(tableData){
return tableData;
});

to return that json_encoded array, would I just put that in a function in mData?

i.e.

"mData": function (source, type, val){
$.post("index.php", {action: 'table_data', user_id: userID}, function(tableData){
return tableData;
});
}

allan

No - you don't need to make the Ajax call yourself - DataTables will do it for you if you use sAjaxSource .

Allan

blainekasten

Adding my file as the sAjaxSource simply results in an alert stating my JSON formatting is wrong. Any tips on debugging? my json format hasn't changed from above. Currently this is my set up:

oTable = $('.dataTable').dataTable( {
"sScrollY": 300, //Turns y scrolling on and sets height
"bJQueryUI": true,
"bPaginate": false,
"bScrollCollapse": false, //Keeps the table at a static height.
//"bFilter": false, //Hides Search bar
"bInfo": false, //Hides bottom bar info
//"sPaginationType": "full_numbers"
"bStateSave": true, //Allows the table state to be kept between logs
"bProcessing": true,
"sAjaxSource": "includes/table_data.php",
"sAjaxDataProp": "",
"aoColumns": [
{"mData": "user_user_id"},
{"mData": "ticket_id"},
{"mData": "short_desc"},
{"mData": "office_name"},
]
}); oTable.fnSetColumnVis( 1, false);

This is a really cool API btw man. You've done some very good work here! I'm thankful you are letting people use your work for their projects! It's very cool.

blainekasten

[code]
oTable = $('.dataTable').dataTable( {
"sScrollY": 300, //Turns y scrolling on and sets height
"bJQueryUI": true,
"bPaginate": false,
"bScrollCollapse": false, //Keeps the table at a static height.
//"bFilter": false, //Hides Search bar
"bInfo": false, //Hides bottom bar info
//"sPaginationType": "full_numbers"
"bStateSave": true, //Allows the table state to be kept between logs
"bProcessing": true,
"sAjaxSource": "includes/table_data.php",
"sAjaxDataProp": "",
"aoColumns": [
{"mData": "user_user_id"},
{"mData": "ticket_id"},
{"mData": "short_desc"},
{"mData": "office_name"},
]
}); oTable.fnSetColumnVis( 1, false);
[/code]

blainekasten

Cleaned up the code for you.. Sorry about the triple whammy there.

[code]
oTable = $('.dataTable').dataTable( {
"sScrollY": 300,
"bJQueryUI": true,
"bPaginate": false,
"bScrollCollapse": false,
"bInfo": false,
"bStateSave": true,
"bProcessing": true,
"sAjaxSource": "includes/table_data.php",
"sAjaxDataProp": "",
"aoColumns": [
{"mData": "user_user_id"},
{"mData": "ticket_id"},
{"mData": "short_desc"},
{"mData": "office_name"},
]
});
oTable.fnSetColumnVis( 1, false);
[/code]

blainekasten

This is the includes/table_data.php file, if it helps
[code]
<?php

function tableData(){
require_once("includes/db_connection.php");
global $pdo;
//extract($post);

$user_id = $_SESSION['USER_ID'];
/* -------------------QUERY/PDO------------------*/
$query = 'SELECT t.user_user_id, t.ticket_id, t.short_desc, a.office_name
FROM ticket t
JOIN account a
ON t.account_id = a.account_id
WHERE user_user_id = :user_id';
$sql = $pdo->prepare($query);
$sql->execute(array(':user_id' => $_SESSION['USER_ID']));

$results = $sql->fetchAll(PDO::FETCH_ASSOC);


//echo json_encode($results);

return json_encode($results);
}
?>
[/code]

blainekasten

Does the issue lie in my lack of using 'aaData' in my php file? If so, how would I implement that?

blainekasten

http://debug.datatables.net/ecutup

allan

Thanks for the debug trace. For the table #currentProjects, the sAjaxSource it pointing at `js/tableData.js` . That is returning:

[code]
var userID = 263;
$.post("index.php", {
action: 'table_data',
user_id: userID
}, function(tableData) {
console.log(tableData);
});
[/code]

which of course is not valid JSON - hence the warning. Are you sure you want to be loading tableData.js?

Allan

blainekasten

That must have been an old debug.
I have found that if I put the sAjaxSource as a .txt file with the returned data from the php file simply typed out in the file, it works just fine. But if I point my sAjaxSource at that .php file, i get an error so I just tried to point it to a js file which runs a post action to that .php file. This server side stuff is hard. If there is anything else I can send to help let me know.

allan

Can you run a current debug or give me a link to the page?

blainekasten

http://debug.datatables.net/efecep

allan

Return from the sever is empty.

blainekasten

Would there be any use for the "fnServerData" function? I tried to modify my setup to be like this:
[code]
"sAjaxSource": "index.php",
"fnServerData": function(){
$.post("index.php", {
action: 'table_data',
success: function(data){
return data;
}
});
},
"sAjaxDataProp": "",
[/code]

This is no longer giving me errors, but my debugger states that 'No XHR request made' in the Last JSON from server.

http://debug.datatables.net/utogos

blainekasten

It seems like there may be some issues with how PDO interacts with the data tables? I've seen a few other posts around here. Have you seen any way to resolve PDO issues?

yor

I'm working with PDO and Datatable server-side. I don't understand your problem. Check in the exemples Datatable, There is a part on server-side version and read a php script!

allan

> Would there be any use for the "fnServerData" function?

No. Unless you need to override the default, which you don't as far as I can see, and it just breaks the debugger, then there is no point is doing so.

It would be extremely helpful, and let me answer your questions much quicker ad more accurately if you could link me to the page you are working on please. Until them I'm just guessing, and from the last debug trace that you linked to all I can say is that the server is responding with an empty page (which is not valid JSON and hence the error).

DataTables itself does not integrate directly with PDO - that is entirely done by your script which acts as a bridge between the Ajax request from DataTables and your database.

Allan

blainekasten

Unfortunately my website is internal, so I'll try to build it for you to see.

http://live.datatables.net/icufor/edit#javascript,live

My php script looks like this:

[code]
<?php

function tableData(){
require_once("includes/db_connection.php");
global $pdo;
//extract($post);

$user_id = $_SESSION['USER_ID'];
/* -------------------QUERY/PDO------------------*/
$query = 'SELECT t.user_user_id, t.ticket_id, t.short_desc, a.office_name
FROM ticket t
JOIN account a
ON t.account_id = a.account_id
WHERE user_user_id = :user_id';
$sql = $pdo->prepare($query);
$sql->execute(array(':user_id' => $_SESSION['USER_ID']));

$results = $sql->fetchAll(PDO::FETCH_ASSOC);


//echo json_encode($results);
$output = array(
"sEcho" => 1,
"iTotalRecords" => 4,
"iTotalDisplayRecords" => 4,
"aaData" => $results
);

return json_encode($output);
}
?>
[/code]

Currently i have static numbers in the $output array for testing and will fix later.
My output if I query that via a $.post is:

[code]
{"sEcho":1,"iTotalRecords":4,"iTotalDisplayRecords":4,"aaData":[{"user_user_id":265,"ticket_id":1467568,"short_desc":"Gastroenterology EMR Forms - Dr. Heiser","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1469185,"short_desc":"Orthopedic Surgery iEHR forms - Joey","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1486511,"short_desc":"Hand Surgery iEHR Forms - Dr. Birndorf","office_name":"Temp Medical, M.D"},{"user_user_id":265,"ticket_id":1487593,"short_desc":"Chiro iEHR \/ Clipboard Forms - Dr. Lawrence","office_name":"Temp Medical, M.D"}]}
[/code]

Right now upon load, i just get the error that says the JSON format is incorrect.

blainekasten

I think ideally what would fix all my issues if you could explain a way that I could send an action: argument with my post to the sAjaxSource.
Basically my queries are all piped through my index.php

So if I call action: table_data on my index.php it runs the above table_data.php file. Is there a way to specify the action sent to an sAjaxSource file?

blainekasten

Got this fixed by doing this:

[code]"sAjaxSource": "index.php?action=table_data",[/code]

blainekasten

Thanks for the help Allan! Last question, what method is called after the entire table is displayed?

allan

Good to hear you got it working.

> what method is called after the entire table is displayed?

fnInitCallback after initialisation and fnDrawCallback after each draw.

Allan