Я использую Datatables Plugin. Он показывает записи правильно, но проблемы возникают, когда я ищу, а затем после поиска его покажу мне некоторые дополнительные записи. Я знаю, что я пробовал до сих пор
ФАЙЛ ФАЙЛА SERVER SIDE:
<?php $statusidbs = 2 $statusidla = 3; $DEPT = 5; $DEPTa = 7; ?> <?php $link = mysql_connect('localhost', 'root', ''); $db_selected = mysql_select_db('test', $link); if (!$db_selected) { die ('Can\'t use foo : ' . mysql_error()); } // the columns to be filtered, ordered and returned // must be in the same order as displayed in the table $columns = array ( "main.id", "pkt.packet_name", "main.new_value", "third.status_message_name", "rolem.role", "role.role", ); $table = "process AS main "; $joins = "LEFT JOIN packetid AS pkt ON main.packet_id=pkt.id LEFT JOIN statusmessage as third ON main.status_id = third.id LEFT JOIN users AS depat ON main.assigned_to_id=depat.id INNER JOIN deptroles AS role ON depat.role_id=role.id LEFT JOIN deptroles AS rolem ON main.dept_role=rolem.id "; //if i am using below where condition in above JOIN then my search is not working // as i want result with only $statusidla , $DEPT but when i search i get result with // $statusidla and $statusidbs // filtering $sql_where = "WHERE main.status_id=".$statusidla." AND main.dept_role=".$DEPT." "; if ($_GET['sSearch'] != "") { $sql_where = "WHERE "; foreach ($columns as $column) { $sql_where .= $column . " LIKE '%" . mysql_real_escape_string( $_GET['sSearch'] ) . "%' OR "; } $sql_where = substr($sql_where, 0, -3); } // ordering $sql_order = ""; if ( isset( $_GET['iSortCol_0'] ) ) { $sql_order = "ORDER BY "; for ( $i = 0; $i < mysql_real_escape_string( $_GET['iSortingCols'] ); $i++ ) { $sql_order .= $columns[$_GET['iSortCol_' . $i]] . " " . mysql_real_escape_string( $_GET['sSortDir_' . $i] ) . ", "; } $sql_order = substr_replace( $sql_order, "", -2 ); } // paging $sql_limit = ""; if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) { $sql_limit = "LIMIT " . mysql_real_escape_string( $_GET['iDisplayStart'] ) . ", " . mysql_real_escape_string( $_GET['iDisplayLength'] ); } $main_query = mysql_query("SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . " FROM {$table} {$joins} {$sql_where} {$sql_order} {$sql_limit}") or die(mysql_error()); // get the number of filtered rows $filtered_rows_query = mysql_query("SELECT FOUND_ROWS()") or die(mysql_error()); $row = mysql_fetch_array($filtered_rows_query); $response['iTotalDisplayRecords'] = $row[0]; // get the number of rows in total $total_query = mysql_query("SELECT COUNT(id) FROM {$table} WHERE main.status_id=".$statusidla." AND main.dept_role=".$DEPT." ") or die(mysql_error()); $row = mysql_fetch_array($total_query); $response['iTotalRecords'] = $row[0]; // send back the sEcho number requested $response['sEcho'] = intval($_GET['sEcho']); // this line is important in case there are no results $response['aaData'] = array(); // finish getting rows from the main query while ($row = mysql_fetch_row($main_query)) { $response['aaData'][] = $row; } // prevent caching and echo the associative array as json header('Cache-Control: no-cache'); header('Pragma: no-cache'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Content-type: application/json'); echo json_encode($response); ?> HTML и AJAX
$(document).ready(function() { $('#example').dataTable( { "bProcessing": true, "bServerSide": true, "sAjaxSource": "scripts/server_processing.php" } ); } ); <table id="example" class="display" cellspacing="0" width="100%"> <thead> <tr> <th>id</th> <th>packet name</th> <th>value</th> <th>status</th> <th>dept</th> <th>deptconct</th> </tr> </thead> <tfoot> <tr> <th>id</th> <th>packet name</th> <th>value</th> <th>status</th> <th>dept</th> <th>deptconct</th> </tr> </tfoot> </table>
Все работает нормально. thats Список записей показан только с $ statusidla.
но когда я ищу, я получаю результат с $ statusidla и $ statusidbs
но я хочу, чтобы мой поиск показывал только запись для $ statusidla
Источник выше: http://datatables.net/forums/discussion/2651/alternative-server-side-php-script и http://datatables.net/examples/server_side/simple.html