Nuv Posted June 6, 2012 Share Posted June 6, 2012 I'm trying to sort by my logbook with datetime. However, i am getting strange results when i use "ORDER BY datetime DESC". I searched over net and realized i need to use CONVERT. Though i am not able to understand it. Can someone please explain me My table structure - CREATE TABLE IF NOT EXISTS `logbook` ( `id` int( NOT NULL AUTO_INCREMENT, `category` varchar(100) NOT NULL, `user` varchar(100) NOT NULL, `description` varchar(300) NOT NULL, `datetime` datetime NOT NULL, `userinfo` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ; Query I am using - "SELECT * FROM `logbook` ORDER BY CONVERT(datetime, date, 103) LIMIT 10" Though i am not getting any result. Can someone please point me in the right direction. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/ Share on other sites More sharing options...
PravinS Posted June 6, 2012 Share Posted June 6, 2012 No need to use "CONVERT", just use "ORDER BY datetime". Also if possible change the field name "datetime" as it is reserved word Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351584 Share on other sites More sharing options...
Nuv Posted June 6, 2012 Author Share Posted June 6, 2012 As i mentioned i already did that. Im getting the following result If you'll notice,it hasn't been sorted in any order(either ASC or DESC of datetime). Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351585 Share on other sites More sharing options...
Illusion Posted June 6, 2012 Share Posted June 6, 2012 try this SELECT * FROM `logbook` ORDER BY (UNIX_TIMESTAMP(`datetime`)) asc Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351595 Share on other sites More sharing options...
Nuv Posted June 6, 2012 Author Share Posted June 6, 2012 Not working. I'm getting the same result. Here is my full code i am using with full logbook.sql dump. <?php $sort = mysql_query("SELECT * FROM `logbook` ORDER BY (UNIX_TIMESTAMP(`datetime`)) ASC"); while ($row = mysql_fetch_array($sort)) { ?> <tr> <td><?php echo $row[2] ?></td> <td><?php echo $row[1] ?></td> <td><?php echo $row[4] ?></td> <td><a href="#"><?php echo $row[3] ?></a></td> </tr> <?php } ?> SQL Dump CREATE TABLE IF NOT EXISTS `logbook` ( `id` int( NOT NULL AUTO_INCREMENT, `category` varchar(100) NOT NULL, `user` varchar(100) NOT NULL, `description` varchar(300) NOT NULL, `datetime` datetime NOT NULL, `userinfo` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ; -- -- Dumping data for table `logbook` -- INSERT INTO `logbook` (`id`, `category`, `user`, `description`, `datetime`, `userinfo`) VALUES (1, 'Backup File', '', 'File added with path = D:/Music/', '2012-06-05 09:09:51', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 10:09:51 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'), (2, 'Backup File', '', 'File added with path = D:/Company/vroomweb/', '2012-06-05 11:19:47', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 12:19:47 PM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'), (3, 'Backup File', '', 'File added with path = D:/Company/Form1.pdf', '2012-06-05 11:19:47', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 12:19:47 PM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'), (7, 'User Login', 'Unknown User', 'Login attempt on admin', '2012-06-06 06:20:06', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:20:06 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}'), (8, 'User Login', 'Unknown User', 'Login attempt on admin', '2012-06-06 06:21:21', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:21:21 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}'), (9, 'User Login', 'admin', 'admin logged in.', '2012-06-06 06:21:53', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:21:53 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}'); Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351604 Share on other sites More sharing options...
Nuv Posted June 6, 2012 Author Share Posted June 6, 2012 Server version: 5.5.16 Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351608 Share on other sites More sharing options...
Pikachu2000 Posted June 6, 2012 Share Posted June 6, 2012 No need to use "CONVERT", just use "ORDER BY datetime". Also if possible change the field name "datetime" as it is reserved word It isn't a reserved word, actually. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351615 Share on other sites More sharing options...
Illusion Posted June 6, 2012 Share Posted June 6, 2012 Seems like version specific issue... following query worked properly in 5.5.24.1 SELECT * FROM `logbook` ORDER BY `datetime` desc; Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351616 Share on other sites More sharing options...
Pikachu2000 Posted June 6, 2012 Share Posted June 6, 2012 It may work in your query due to the fact that you enclosed datetime in backticks, and the OP did not. Without the backticks it may be ambiguous, so they could be needed in this instance. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351619 Share on other sites More sharing options...
Illusion Posted June 6, 2012 Share Posted June 6, 2012 I didn't understand this part <tr> <td><?php echo $row[2] ?></td> <td><?php echo $row[1] ?></td> <td><?php echo $row[4] ?></td> <td><a href="#"><?php echo $row[3] ?></a></td> </tr> if you are scrambling the rows ... how do you expect them to be displayed in the same order you have fetched the records from database? Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351620 Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2012 Share Posted June 6, 2012 In each row fetched - $row[0] is the `id` $row[1] is the `category` $row[2] is the `user` $row[3] is the `description` $row[4] is the `datetime` $row[5] is the `userinfo` Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351621 Share on other sites More sharing options...
Illusion Posted June 6, 2012 Share Posted June 6, 2012 @PFMaBiSmAd Oops... thanks for clarifying. I should have looked at HTML carefully . Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351623 Share on other sites More sharing options...
Nuv Posted June 6, 2012 Author Share Posted June 6, 2012 It may work in your query due to the fact that you enclosed datetime in backticks, and the OP did not. Without the backticks it may be ambiguous, so they could be needed in this instance. Funny after using "SELECT * FROM logbook ORDER BY `datetime` ASC" , i get And after using "SELECT * FROM logbook ORDER BY `datetime` DESC", i get Why am i getting unexpected results ? :'( Although, time is perfect when i use 'DESC' but not the dates. I really wanted to sort this only with SQL and not using Php. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351641 Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2012 Share Posted June 6, 2012 The display you have posted is the result of more code that you have shown. If this isn't due to a bug in mysql, then it's likely something your code on the page is doing. If you have a whole page that doesn't work as expected, you would need to post all the code needed to reproduce that page in order to get help with the problem. When you run the query directly in your favorite database management tool, do you get the expected results? Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351643 Share on other sites More sharing options...
PFMaBiSmAd Posted June 6, 2012 Share Posted June 6, 2012 In fact, it looks to me like your code is sorting by the username, categories, then the datetime values within each group of username/categories. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351658 Share on other sites More sharing options...
Illusion Posted June 6, 2012 Share Posted June 6, 2012 Seems like the data sorted by javascript. You need to check that. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351667 Share on other sites More sharing options...
Jessica Posted June 6, 2012 Share Posted June 6, 2012 In fact, it looks to me like your code is sorting by the username, categories, then the datetime values within each group of username/categories. Agreed. OP, check the rest of the code. And as suggested run the query in phpMyAdmin or similar. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351671 Share on other sites More sharing options...
ignace Posted June 6, 2012 Share Posted June 6, 2012 Like PFMaBiSmAd already pointed out your table appears to be sorted by username. CTRL+U and view the source whether it has been correctly sorted there. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1351695 Share on other sites More sharing options...
Nuv Posted June 8, 2012 Author Share Posted June 8, 2012 Seems like the data sorted by javascript. You need to check that. Yup Its sorted out by javascript. Sorry for the trouble guys, i figured after PFMaBiSmAd's post. Quote Link to comment https://forums.phpfreaks.com/topic/263748-datetime-convert-trying-to-sort-by-date-by-datetime/#findComment-1352106 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.