25th Sep 2015sorting by not working correctly server side
$(document).ready( function () {
$('#server_side').DataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "processa.php",
"bLengthChange":false,
"iDisplayLength": 20,
"oLanguage": {
"oPaginate": {
"sNext": "Próximo",
"sPrevious": "Anterior"
}
},
"aoColumnDefs": [{
'bSortable': false,
'aTargets': [0]
}]
});
} );
<table class="table table-striped table-bordered table-hover" id="server_side">
<thead>
<tr>
<th class="no-sort"></th>
<th><font color="black">Id</font></th>
<th><font color="black">Nome</font></th>
<th><font color="black">URL</font></th>
<th><font color="black">Telefone</font></th>
<th><font color="black">Categoria</font></th>
</thead>
</table>
header("Content-Type: text/json; charset=ISO-8859-1");
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
//$aColumns = array('id', 'name', 'status');
$aColumns = array('id', 'name','url','phone','category');
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";
/* DB table to use */
$sTable = "sites_novo";
//$sTable = "category";
/*
* Paging
*/
$sLimit = "25";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . mysql_real_escape_string($_GET['iDisplayStart']) . ", " .
mysql_real_escape_string($_GET['iDisplayLength']);
}
/*
* Ordering
*/
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if($auth->getAuthData("usu_ver_tds_site") == 0){
if ($sWhere == "") {
$sWhere = " WHERE users_id = ".$auth->getAuthData("usu_id");
}else{
$sWhere = " AND users_id = ".$auth->getAuthData("usu_id");
}
}
if ($_GET['sSearch'] != "") {
$sWhere = "WHERE (";
for ($i = 0; $i < count($aColumns); $i++) {
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . "
FROM $sTable
$sWhere
$sOrder
$sLimit
";
//$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$rResult = $mysql->Query($sQuery);
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $mysql->Query($sQuery);
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(" . $sIndexColumn . ")
FROM $sTable
";
$rResultTotal = $mysql->SelectOne($sQuery);
//$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $rResultTotal;
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
$id = $aRow[id];
for ($i = 0; $i < count($aColumns); $i++) {
$row[0] = '<div class="checker" id="uniform-1">'
. '<span><input type="checkbox" id="' . $id . '" class="checkboxes" style="opacity: 0;" name="deletar[]" value="1"></span>'
. '</div>'
. '<img class="edit_list" alt="Editar" src="img/edit.png" onclick=\'window.open ("site_edit.php?id=' . $id . '", "", "")\' style="cursor:pointer;">'
. '<img class="del_list" alt="Excluir" src="img/del.png" rel="' . $id . '" style="cursor:pointer;">';
if ($aColumns[$i] == "category") {
$row[] = utf8_encode($mysql->SelectOne("SELECT name FROM category WHERE id =" . $aRow[$aColumns[$i]]));
} else if ( $aColumns[$i] != ' ' ){
$row[] = utf8_encode($aRow[$aColumns[$i]]);
}
}
$output['aaData'][] = $row;
}
echo json_encode($output);
he orders more not correctly