unable to conduct serverside processing with DataTables V1.10 within a Wordpress development site
unable to conduct serverside processing with DataTables V1.10 within a Wordpress development site
A user visiting my site should be able to choose from a series of form values, which after serialization is then sent through DataTables Ajax call to a function wthin wordpresses function.php file that queries a MySQL database and returns the retrieved data in JSON format.
The output I am getting is that the data table appears with column headings in place, but no records are included within it. Instead, I am receiving the following statement:
Showing 0 to 0 of 0 entries (filtered from NaN total entries)
Upon checking DataTables debug feature (my code is ovodas), it looks like a JSON value of 0 is being returned rather than the records I'd anticipated. However, I'm uncertain as to why, as previous AJAX calls placed outside the DataTables framework returned valid JSON.
link to live.datatables.net containing html user form and Jquery DataTables initialization
http://live.datatables.net/sefumowo/1/edit?html,js
server-side script is below
<?php
function query_database(){
$queried_columns = array();
$queried_fields = array();
foreach ($_POST as $column => $field) {
if (isset($field) && !empty($field)){
if ($column == 'patient_id') {
$queried_columns[] = "$column = '%s' ";
$queried_fields[] = "$field ";
}
if ($column == 'sequence_id') {
$queried_columns[] = "$column = '%s' " ;
$queried_fields[] = "$field ";
}
...
if ($column == 'brkp_type') {
if(count($field) == 1 ) {
$queried_columns[] = "$column = '%s' ";
$queried_fields[] = "$field[0] ";
}
else {
$queried_columns[] = "$column in ('%s','%s') ";
$queried_fields[] = "$field[0] ";
$queried_fields[] = "$field[1] ";
}
}
...
if ($column == "start_point" || $column == "end_point") {
$value = $_POST[$column][0];
if (count($field) == 3 && $value == "between" || $value == "not between") {
$queried_columns[] = "$column $value '%d' AND '%d' ";
$queried_fields[] = "$field[1] ";
$queried_fields[] = "$field[2] ";
}
if (count($field) == 2 && $value != "between") {
$queried_columns[] = "$column $value '%s' ";
$queried_fields[] = "$field[1] "; }
if (count($field) == 1) {
continue;
}
}
}
}
if(is_admin()) { $mydb = new wpdb( 'x', 'x', 'x', 'x' );}
elseif(is_user_logged_in()) { $mydb= new wpdb( 'x' , 'x' , 'x' , 'x' );}
else { $mydb = new wpdb( 'x' , 'x' , 'x' , 'x' );}
$table_name = 'breakpoint_query';
$query_frame = 'SELECT * FROM '.$table_name.' WHERE '.implode(' AND ', $queried_columns).' ORDER by
breakpoint_query.breakpoint_id;';
$sanitized_query = $mydb->prepare($query_frame, $queried_fields);
$records = $mydb->get_results($sanitized_query);
$data = $records;
$json_data = array(
"draw" => intval( $_POST['draw']),
"recordsTotal" => intval( $_POST['totaldata'] ),
"recordsFiltered" => intval( $_POST['totalfiltered'] ),
"data" => $data
);
wp_send_json($json_data);
}
add_action('wp_ajax_query_database', 'query_database');
add_action('wp_ajax_nopriv_query_database', 'query_database');
Please let me know if you would like me to provide more information as to the scope of my problem if anything is unclear. I'm happy to try and help.
Answers
link to codepad containing server side script in proper format
http://codepad.org/vIfqCYh7