Column sort wrong, data always string?

hawkmaster

Hello,
I have a stange problem.
One column "Rating" shows numbers. But the sorting is wrong if I click on the column head.
e.g
0
10
10
100
40
70

The column in the MySQL database is definitly a "int" type.
There are also no blanks or alphanumeric character in this column.
Also "sType": "numeric", makes no changes of sorting.

What I wonder is that a check with "return typeof(data);" shows always "string" for all rows.

What could be the problem and how can I sort correct like:
0
10
10
40
70
100

"aoColumns":
[
{ "mData": "rating",
"sWidth": 50,
"sType": "numeric",

            "render": function ( data, type, row ) {
                //return data +'%';
                return typeof(data);
            }

        },

PHP Code

$sqldatajson = json_encode($sqlprojectdata);
echo $sqldatajson ;

best regards
Hans

John Archer

How does $sqldatajson look like? I guess you have to put $sqldatajson into an additional object, like so:

$myData = new stdClass();

$myData->sqlprojectdata= $sqlprojectdata;

$sqldatajson = json_encode($myData );

echo $sqldatajson;

I think this preserves the correct data type (not entirely sure about that, but I had a similar problem and I think that was the solution).

hawkmaster

Thanks John for help,

The php code is in a separate php file. This is called in JS with Ajax

oTable = $("#projectdata").dataTable({
"sAjaxSource": 'getdata.php',
"sAjaxDataProp": "",
"autoWidth": false,
"aaSorting": [[ 0, "asc" ]],
"scrollY": "200px",
"scrollCollapse": true,
"paging": false,
....

In PHP I get the MySqL data with PDO.

getdata.php

$sqldata = get_MyData();

$sqldatajson = json_encode($sqldata );
echo $sqldatajson ;

What I wonder why the MySQL interger column is interpreted as string in datatable and why a sType": "numeric" does also not work?

tangerine

The column in the MySQL database is definitly a "int" type.

MySQL returns integers as strings. You can't rely on its datatypes when retrieving data.

I thought sType:numeric would suffice - are you saying it doesn't work (without the "render")?

hawkmaster

yes it does not suffice with sType numeric, also without the render function

{ "mData": "rating",
"sWidth": 50,
"sType": "numeric"
},

What I have tested now is sType = natural

{ "mData": "rating",
"sWidth": 50,
"sType": "natural"
},

Then the sort order is ok
0
10
10
40
70
100

But I wonder why I need a special natural function and why it does not work with "numeric"?

hawkmaster

yes it does not suffice with sType numeric, also without the render function

{ "mData": "rating",
"sWidth": 50,
"sType": "numeric"
},

What I have tested now is sType = natural

{ "mData": "rating",
"sWidth": 50,
"sType": "natural"
},

Then the sort order is ok
0
10
10
40
70
100

But I wonder why I need a special natural function and why it does not work with "numeric"?