PHP / Datatables：数字按文本排序

Question

I am using Datatables to style a table that I fill with PHP.

Everything works as intended except for a column containing amounts. If I click on the Datatables column header for this column in order to sort the table by this column it sorts it but only as text instead of numbers so that 20 appears before 3 etc.

Is there a way I can prevent this in PHP by changing the format ?

Example cell:

<td class="colAmount ralign"><?php echo number_format(doubleval($requests->amount), 2, ".", ","); ?></td>

If an amount in my database ($requests->amount) is e.g. 100000 then the above would format this as 100,000.00 as I need to display it with the thousands and decimal separators - this is then probably interpreted as text.

Many thanks in advance, Tim.

Answer 1

You can use Datatables Orthogonal Data feature for this. This way, you can use different formats for display and sorting. There are two approaches here, I would go for the HTML 5 style, which uses data attributes (from the example there):

<tr>
    <td data-search="Tiger Nixon">T. Nixon</td>
    <td>System Architect</td>
    <td>Edinburgh</td>
    <td>61</td>
    <td data-order="1303682400">Mon 25th Apr 11</td>
    <td data-order="3120">$3,120/m</td>
</tr>

In this example the data for the last two columns would be sorted using the data-order attributes. There should be no further configuration needed, Datatables should detect these attributes automatically.