CODEWITHSUNDEEP
phpmysqlsortingdatatable
Nakshatra
Nakshatra

Reputation: 55

datatable listing issue of sort order

I am using datatable. I am using the following mysql query

$sql="select * from table ORDER BY STR_TO_DATE(  `pdate` ,  '%d/%m/%Y' ) desc";

But this ORDER BY Date is not working. It is not listing the data in the order of pdate desc. It is listing data randomly

pdate is storing in the table in the format '29/09/2016'

when I am running this query in database table it is listing properly but in datatable sorting order is not correct.it is listing in the ascending order of first column in the datatable listing

sql query
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pdate` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `name`, `pdate`) VALUES
(1, 'a', '29/09/2016'),
(2, 'b', '29/09/2015'),
(3, 'c', '29/02/2016'),
(4, 'd', '22/05/2016'),
(5, 'e', '27/06/2015');

table display

    <script src="jstables/jquery-1.12.3.js"></script>
<script src="jstables/jquery.dataTables.min.js"></script>
<script src="jstables/dataTables.buttons.min.js"></script>
<script src="jstables/buttons.flash.js"></script>
<script src="jstables/jszip.js"></script>
<script src="jstables/pdfmake.js"></script>
<script src="jstables/vfs_fonts.js"></script>
<script src="jstables/buttons.html5.js"></script>
<script src="jstables/buttons.print.js"></script>
<link rel="stylesheet" href="csstables/jquery.dataTables.css" />
<link rel="stylesheet" href="csstables/buttons.dataTables.css" />
$querystring="select * from test ORDER BY STR_TO_DATE(  `pdate` ,  '%d/%m/%Y' ) desc";
$sql=mysql_query($querystring);
<table id="example" border="1" class="display nowrap" cellspacing="0" width="100%"><thead>
<tr><th>aws</th><th>dd</th></tr></thead><tbody>
<?php
while($res=mysql_fetch_array($sql)){ 
?>
<tr><td><?php echo $res['name'];?></td><td><?php echo $res['pdate'];?></td></tr>
<?php
}
?>
</tbody>
</table>
<script type="text/javascript">
    $(document).ready(function() {
    $('#example').DataTable( {

        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ]
    } );
} );
    </script>

Upvotes: 1

Views: 2293

Answers (3)

Ikhsan
Ikhsan

Reputation: 51

try add order parameter after your dom code on datatable like this :

$('#table').DataTable({
    "order": [[2, "desc"]] //"2" is my date array position
    .......
});

Upvotes: 2

Jazzzzzz
Jazzzzzz

Reputation: 1633

You are storing date in varchar datatype column.

so when you do order by it will treat as text not date.

you need to type casting for that 

select * from table ORDER BY CAST(`pdate` AS DATE) desc

Reference https://dev.mysql.com/doc/refman/5.5/en/cast-functions.html

Upvotes: 0

Amy
Amy

Reputation: 4032

Try This:

$sql="select * from table where name='$name' ORDER BY `pdate` desc";

Upvotes: 0

Related Questions