Jump to content

Recommended Posts

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)

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?

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.

^^^^ 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.

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

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.

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.

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 ().

^^^^ 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.

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.