PHP json_encode help
PHP json_encode help
blainekasten
Posts: 18Questions: 0Answers: 0
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!
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!
This discussion has been closed.
Replies
Allan
$.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
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.
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]
[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]
[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]
[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
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.
[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
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
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.
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?
[code]"sAjaxSource": "index.php?action=table_data",[/code]
> what method is called after the entire table is displayed?
fnInitCallback after initialisation and fnDrawCallback after each draw.
Allan