The Little Guy Posted September 7, 2012 Share Posted September 7, 2012 I don't believe MySQL has a natural sorting capability. So, you might need to query the records, dump into an array and then sort using PHP's natural sorting capability It does't, and from what I have read my two queries above should allow for natural sorting, so I don't think it is the issue. Wanna post the results of this? show create table files; what is the Collation of the column? that is more than likely the problem, as it has to do with sorting (if I am not mistaken) Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 Collation of the column is varchar utf8_general_ci Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 Maybe AB51MN50.jpg cannot appear 4th in the list before AB51MN100.gif because 1 comes before 5? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted September 7, 2012 Share Posted September 7, 2012 try using: latin1_swedish_ci Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 7, 2012 Share Posted September 7, 2012 Alphanumerically, AB51MN50.jpg should come after AB51MN175eue.gif You need natural case sorting, but even that may not do it. AB51MN1... AB51MN5... See? Maybe AB51MN50.jpg cannot appear 4th in the list before AB51MN100.gif because 1 comes before 5? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 7, 2012 Share Posted September 7, 2012 You are gong to need to get JUST the file name part (without the extension) and use a natural sort on it. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted September 7, 2012 Share Posted September 7, 2012 You are gong to need to get JUST the file name part (without the extension) and use a natural sort on it. Going off that... put the extension in a separate column, that is what i always do when saving the files. Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 try using: latin1_swedish_ci I am using latin1_swedish_ci on my file_type column. Can I just change it in PHPMyadmin? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted September 7, 2012 Share Posted September 7, 2012 go to the table structure and click the edit button for the column, in there you can change it. Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 7, 2012 Share Posted September 7, 2012 You are gong to need to get JUST the file name part (without the extension) and use a natural sort on it. Going off that... put the extension in a separate column, that is what i always do when saving the files. Looks like OP already has that part, judging by the var dump, but needs to remove it from the existing names too. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 7, 2012 Share Posted September 7, 2012 ^^^^ Speaking of which, the dump of the file_type field shows a huge number of non-printing/white-space characters that need to be trimmed. Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 ^^^^ Speaking of which, the dump of the file_type field shows a huge number of non-printing/white-space characters that need to be trimmed. That is just from me doing a copy and paste to notepad, formatting and then pasting to the forum. The extension is stored as part of the file. The other column is file_type but I am not using that. Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 Would this be correct to run in PHPMyadmin to trim the empty space from beginning of files? UPDATE file_name set file_name = ltrim(file_name) WHERE id >0; Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 7, 2012 Share Posted September 7, 2012 I don't know why you have a WHERE on it Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 I don't know why you have a WHERE on it So is this correct to trim just the empty space at beginning of file name? UPDATE file_name set file_name = ltrim(file_name); Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 7, 2012 Share Posted September 7, 2012 Sure Quote Link to comment Share on other sites More sharing options...
Barand Posted September 7, 2012 Share Posted September 7, 2012 UPDATE table_name SET file_name = LTRIM(file_name) Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 7, 2012 Share Posted September 7, 2012 Whoops, didn't catch that good eye Barand Quote Link to comment Share on other sites More sharing options...
mallen Posted September 7, 2012 Author Share Posted September 7, 2012 Ok that cleared it up. Well I am about to close this thread. I think I have what I need. There is only so much I can do to make these files in order. In this list below technically are they in order? 'AB51MN50.jpg' and 'AB51MN100.jpg' are the same in the first six characters: 'AB51MN'. The seventh character is different: '5' versus '1'. One is less than 5, so 'AB51MN100.jpg' comes first. AB51HPS70.gif<--- AB51MN100.jpg AB51MN100.gif AB51MN175 w.jpg AB51MN175 w.gif AB51MN175 w.jpg AB51MN175 w.gif AB51MN175 w.jpg AB51MN175 w.gif AB51MN175.jpg AB51MN175.gif AB51MN175eue.jpg AB51MN175eue.gif AB51MN50.jpg<--- AB51MN50.gif AB51MN70.jpg AB51MN70.gif AB51PS150.jpg AB51PS150.gif Quote Link to comment Share on other sites More sharing options...
Christian F. Posted September 7, 2012 Share Posted September 7, 2012 try using: latin1_swedish_ci I'm afraid this is some really bad advice, seeing as he's already using UTF-8. Granted, in this case it's not much of a big deal, seeing as its apparently only ASCII-compatible characters in the mix. Which is the only reason why it didn't blow up in his face, corrupting the data to the point that he'd need to restore every single non-ASCII character, by hand. If anything, he should have been using any of the non-general UTF-8 charsets. Not that they'd have any impact on this problem, anyway, as it's not a character set issue (but a sorting algorithm issue). mallen: Yeah, as you've found out I'm afraid that the only real solution you have on this is to write your own sorting algorithm. Not something I'd recommend, unless it's really important that the names are in the expected order. Quote Link to comment Share on other sites More sharing options...
Psycho Posted September 8, 2012 Share Posted September 8, 2012 Ok that cleared it up. Well I am about to close this thread. I think I have what I need. There is only so much I can do to make these files in order. In this list below technically are they in order? 'AB51MN50.jpg' and 'AB51MN100.jpg' are the same in the first six characters: 'AB51MN'. The seventh character is different: '5' versus '1'. One is less than 5, so 'AB51MN100.jpg' comes first. For an alphanumerical sort - yes they are in the right order. I already provided you a solution to get them sorted in a natural order, as you are requesting. You will just have to do it using PHP code after you query the results. Quote Link to comment Share on other sites More sharing options...
Christian F. Posted September 8, 2012 Share Posted September 8, 2012 Yeah, as you've found out I'm afraid that the only real solution you have on this is to write your own sorting algorithm. Not something I'd recommend, unless it's really important that the names are in the expected order. Correction: It seems like natcasesort () does indeed do what you want, as evidenced by this result. (Though, upon thinking about it, I really should have realized it right away. ) Array ( [0] => AB51HPS70.gif [14] => AB51MN50.gif [13] => AB51MN50.jpg [16] => AB51MN70.gif [15] => AB51MN70.jpg [2] => AB51MN100.gif [1] => AB51MN100.jpg [8] => AB51MN175 w.gif [6] => AB51MN175 w.gif [4] => AB51MN175 w.gif [3] => AB51MN175 w.jpg [5] => AB51MN175 w.jpg [7] => AB51MN175 w.jpg [10] => AB51MN175.gif [9] => AB51MN175.jpg [12] => AB51MN175eue.gif [11] => AB51MN175eue.jpg [18] => AB51PS150.gif [17] => AB51PS150.jpg ) However, do not that it does not change the keys for the values so a for () loop would yield the unsorted result. You'll need to use foreach (). Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted September 8, 2012 Share Posted September 8, 2012 ^^^^ Speaking of which, the dump of the file_type field shows a huge number of non-printing/white-space characters that need to be trimmed. That is just from me doing a copy and paste to notepad, formatting and then pasting to the forum. The extension is stored as part of the file. The other column is file_type but I am not using that. ["file_id"]=> string(2) "46" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.gif ["file_type"]=> string(15) "GIF } [18]=> array(4) { ["file_id"]=> string(2) "47" ["prod_id"]=> string(1) "5" ["file_name"]=> string(30) "AB51MN175 w.gif ["file_type"]=> string(15) "GIF } [20]=> array(4) { So your copy and paste changed the length values in the parentheses? That's rather curious, especially since the lack of a closing quote makes it look like you may have manually removed a bunch of spaces. Quote Link to comment Share on other sites More sharing options...
Psycho Posted September 8, 2012 Share Posted September 8, 2012 Yeah, as you've found out I'm afraid that the only real solution you have on this is to write your own sorting algorithm. Not something I'd recommend, unless it's really important that the names are in the expected order. Correction: It seems like natcasesort () does indeed do what you want, as evidenced by this result. (Though, upon thinking about it, I really should have realized it right away. ) . . . However, do not that it does not change the keys for the values so a for () loop would yield the unsorted result. You'll need to use foreach (). You do realize that he is dealing with a multidimensional array, right? natcasesort() only works on one-dimensional array. I already provided a solution back on page two but apparently some chose to ignore it. Query the records and put into an array using the file name as the key. Then use ksort() with the SORT_NATURAL flag. Quote Link to comment Share on other sites More sharing options...
Christian F. Posted September 8, 2012 Share Posted September 8, 2012 Hmmm... Must have lost track of that in the thread, though it was a 1-dim array. In that case, do what Psycho said. Thanks for correcting my assumptions. Quote Link to comment 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.