Column sorting is not working correctly

michelmir

Hello.

I'm working with DataTable (1.10.19) on my project and this table have 3 columns: ID / FIRST_NAME / LAST_NAME. What happens is when i try to sort first_name column, DataTables sort by id and not by A-Z/Z-A. Below are the codes that fetch and display table on page using Ajax:

            var dataTable = $('#user_data').DataTable({
            
                "processing":true,
                "serverSide":true,
                "order":[],
                "ajax":{
                    url:"fetch.php",
                    type:"POST"
                },
                     
                "columnDefs":[
                    {
                        "targets":[0, 3, 4],
                        "orderable":false,
                    },
                ],

            });

The script-server (fetch.php) below send results to the Ajax code above and display on page:

$query = '';
    $output = array();
    $query .= "SELECT * FROM users ";
    if(isset($_POST["search"]["value"]))
    {
        $query .= 'WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" ';
        $query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
    }
    if(isset($_POST["order"]))
    {
        $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
    }else {
    
        $query .= 'ORDER BY id ASC ';
    
    }
    
    if($_POST["length"] != -1)
    {
        $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
    }
    $statement = $connection->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    $data = array();
    $filtered_rows = $statement->rowCount();
    foreach($result as $row)
    {
        $sub_array = array();
        $sub_array[] = $image;
        $sub_array[] = $row["first_name"];
        $sub_array[] = $row["last_name"];
        $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
        $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
        $data[] = $sub_array;
    }
    $output = array(
        "draw"              =>  intval($_POST["draw"]),
        "recordsTotal"      =>  $filtered_rows,
        "recordsFiltered"   =>  get_total_all_records(),
        "data"              =>  $data
    );
    echo json_encode($output);

I researched on the manual and i found some articles related with DataTable sorting:

https://datatables.net/reference/api/sort()
https://datatables.net/reference/option/order
https://datatables.net/reference/option/ordering

But unfortunatly i couldn't solve the problem. Just to demonstrate a description of my doubt, this link http://demo.webslesson.info/php-pdo-ajax-crud-datatables/ display a DataTable that not sorting A-Z/Z-A according to my doubt.

In this case do i have to change something on php file code (fetch.php)?

Thanks anyway

colin

Hi @michelmir ,

It looks like it's sorting to me - lower-case first, then upper-case - it would be worth adding more values in to confirm.

Cheers,

Colin

michelmir

Hi @colin.

Thanks for your feedback. Actually i didn't insert the id collumn on table. I inserted on line 30 of script file, this code:

$sub_array[] = $row["id"];

and sorting works fine. I don't know why this happened. Before this, all collumns were be sorting by id. After i inserted this code line, every thing worked.

Thanks