sql order by desc = 41.200 i'm sorting through the site 0, getting the ranking of the first page
my problem is that the ranking only does it on page 1. when I sort the example on page 5, page 1 goes back again
when I do sorting, I want large-to-small sample sql, as in the sorting and pagination example
index.php
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>DataTable Jump to a Specific Page with PHP Ajax</title>
<!-- JS, Popper.js, and jQuery -->
<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<!-- CSS only -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css" />
</head>
<body>
<div class="container-fluid">
<div class="row content">
<div class="col-sm-2">
<div class="well" style="margin-top:120px;">
</div>
</div>
<div class="col-sm-8 text-left">
<br />
<br />
<div class="card">
<div class="card-header">
<div class="row">
<div class="col-md-9">Customer Data</div>
<div class="col-md-3">
<div class="input-group">
<div class="input-group-prepend">
<span class="input-group-text">Page</span>
</div>
<select name="pagelist" id="pagelist" class="form-control"></select>
<div class="input-group-append">
<span class="input-group-text">of <span id="totalpages"></span></span>
</div>
</div>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table id="customer_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>code</th>
<th>date</th>
<th>debit</th>
<th>credit</th>
<th>LINE DESCRIPTION/th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
<div class="col-sm-2">
</div>
</div>
</div>
<br />
<br />
</body>
</html>
<script type="text/javascript" language="javascript">
$(document).ready(function(){
function load_data(start, length)
{
var dataTable = $('#customer_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
//"dom": 'Bfrtip',
"retrieve": true,
"ajax" : {
url:"fetch.php",
method:"POST",
data:{start:start, length:length}
},
"drawCallback": function( settings ){
var page_info = dataTable.page.info();
console.log(page_info);
$('#totalpages').text(page_info.pages);
var html = '';
var start = 0;
var length = page_info.length;
for(var count = 1; count <= page_info.pages; count++)
{
var page_number = count - 1;
html += '<option value="'+page_number+'" data-start="'+start+'" data-length="'+length+'">'+count+'</option>';
start = start + page_info.length;
}
$('#pagelist').html(html);
$('#pagelist').val(page_info.page);
}
});
}
load_data();
$('#pagelist').change(function(){
var start = $('#pagelist').find(':selected').data('start');
var length = $('#pagelist').find(':selected').data('length');
load_data(start, length);
var test_table = $('#customer_data').dataTable();
var page_number = parseInt($('#pagelist').val());
test_table.fnPageChange(page_number);
});
});
</script>
fetch.php
$connect = new PDO("sqlsrv:Server=$server;Database=$database", $kullaniciadi, $sifre);
$column = array("ID","KOD", "TARIH", "BORC", "ALACAK","BAKIYE");
$where = "";
if(isset($_POST["search"]))
{
$where .= " where SATIR_ACIKLAMA LIKE '%".$_POST["search"]["value"]."%' ";
}
$order = "";
if ($_POST["length"] != -1) {
$query = "SELECT *
FROM (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY ID ASC) AS Rn
FROM WWWW_KASAHAREKET () ".$where." ".$order." ) ";
$query .=
"sub WHERE Rn BETWEEN ".($_POST["start"])." AND ".($_POST["start"] + $_POST["length"] - 1 );
}
if(isset($_POST['order'])){
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir']. ' ';
}
else
{
$query .= 'ORDER BY BORC DESC ';
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['ID'];
$sub_array[] = $row['KOD'];
$sub_array[] = $row['TARIH'];
$sub_array[] = $row['BORC'];
$sub_array[] = $row['ALACAK'];
$sub_array[] = $row['SATIR_ACIKLAMA'];
$data[] = $sub_array;
}
function get_total_all_records($connect)
{
$statement = $connect->prepare("SELECT * FROM WWWW_KASAHAREKET () ");
$statement->execute();
$result = $statement->fetchAll();
return $statement->rowCount();
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $number_filter_row,
"recordsFiltered" => get_total_all_records($connect),
"data" => $data
);
echo json_encode($output);
?>
