Sorting Issue

Sorting Issue

RookieProgrammerRookieProgrammer Posts: 4Questions: 3Answers: 0

hi Everyone,

I Am trying to sort the datatable. i am taking dynamic Variable in "Order by Field" however it's not sorting out so kindly guide if i am missing something...


<?php include_once 'db_config.php'; ini_set('display_errors', '1'); ini_set('display_startup_errors', '1'); error_reporting(E_ERROR); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> <script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script> <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css"/> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous"> <title>Pagination-2</title> </head> <body> <div class="container"> <table id="example" class="table table-hover table-striped"> <thead> <tr> <th>User Id</th> <th>User name</th> <th>First Name</th> <th>Last Name</th> <th>Email </th> <th>Gender</th> </tr> </thead> </table> </div> <script> $(document).ready(function() { $('#example').DataTable( { //"processing": true, "serverSide": true, "searching":true, "order":[], "ajax": { 'url':"pagi.php", 'method':"POST", }, // "columns": [ // {"name": "user_id"}, // {"name": "fname"}, // {"name": "lname"}, // {"name": "eml"}, // {"name": "gnd"} // ] }); }); </script> </body> </html>
<?php

include_once 'db_config.php';

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ERROR);

//$searchInput=$_GET['search-by-column'];
$start = $_POST['start'];
$end = $_POST['length'];
$strInput=$_POST['search']['value'];
$columnIndex=$_POST['order'][0]['column'];
//echo $columnIndex;
$columnSortOrder=$_POST['order'][0]['dir'];
//echo $columnSortOrder;

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

//echo "SELECT * FROM user_list WHERE (user_name LIKE '%$strInput%' OR fname LIKE '%$strInput%' OR lname LIKE '%$strInput%') ORDER BY '$columnIndex' '$columnSortOrder' LIMIT $start,$end";

$sql = $conn->query("SELECT * FROM user_list WHERE (user_name LIKE '%$strInput%' OR fname LIKE '%$strInput%' OR lname LIKE '%$strInput%') ORDER BY '$columnIndex' '$columnSortOrder' LIMIT $start,$end");  
//print_r ($sql);

$sql->setFetchMode(PDO::FETCH_ASSOC);

$sql1 = $conn->query("SELECT * FROM user_list");  
$sql1->setFetchMode(PDO::FETCH_ASSOC);
//$page_result = $sql;  
$total_records = $sql1->rowCount(); 
$data = array();
while($row = $sql->fetch()){ 
    $data[] = array(
            $row['user_id'],
            $row['user_name'],
            $row['fname'],
            $row['lname'],
            $row['eml'],
            $row['gnd']
        );
};
$output  = array(
    'draw' => $_POST['draw'],
    'recordsTotal' => $total_records,
    'recordsFiltered' => $total_records,
    'data' => $data,
);
echo json_encode($output);
?>  

Best Regards,
Jay Patel

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    Answer ✓

    You have serverSide enabled, so all sorting, fitlering and paging need to be done by the server.

    If you have less than 10k rows, then just remove the serverSide option. If you have more than that, then have a look at this blog post which shows how you can use our Editor libraries to do server-side processing. Or your can implement it yourself using the manual page I linked to above.

    Allan

This discussion has been closed.