Number sorting . Ascending / Number . High – Low ?

[annegirly]

Hey
 

Please help.
I sell comics / books.
But each strip has a number in album series and this I would like to have sorted in ascending to first released  up to last released.

 

Number  . Ascending ( Customer can choose for this option)
Number  . High – Low ( Customer can choose for this option)

 

Some kind of book series have much releases like 300 pieces or something
Now everything its mixed up on the product pages  ( number 200 is next to number 2 & number 22 by example )

 

We want to know how we have to customize this option .

 

By example ..A customer want to see from a Asterix Collection 1 t/35 then he or she want to see like this:

 

1 , 2 ,3, 4 ,5 ,6 ,7 ,8 ,9 ,10 ,11, 12, 13, 14, 15, 16, 17, 18, 19 ,20, 21 ,22 ,23 ,24, 25, 26 ,27 ,28 ,29 , 30 , 31 ,32, 33 ,34 ,35 .

 

Or they want to see the newest or last release First : number 35 , 34 ,33 ,32 ,31, 30 ,29 , 28 , 27 , 26 , 25 ,24 ,23 ,22 ,21 ,20 ,19 , 18 ,17 ,16 ,15, 14 ,13 , 12 ,11 ,10 ,9 , 8 , 7, 6 , 5, 4 , 3, 2, 1,

 

We really hope for a solution because also our competitors have already this nice functions for their customers

 

Greeting,

 

leespleziervoorjou

 

I use prestashop 1.6.9

Edited February 21, 2015 by annegirly (see edit history)

[PascalVG]

In your example, it seems that the number is regarded as a string, instead of  number, so every 'string' starting with 2 comes before 3, so '224' comes before '3', as it starts with a 2.

 

A non-programming solution would be to change the number from 2 into 002 instead, so it would put 003 before 224, as 0 comes before 2.

 

Is the number part of the product name? Or a separate field, like attribute or so?

 

 

'groetjes',

pascal

[FullCircles]

Yes, we've had clients with similar problems in the past, the issue is indeed that mysql's default ordering treats those numbers as strings

 

PascalVG's solution is the best one certainly, if it's a custom field, you might also be able to change it to be an integer rather than a varchar, which might also get it to use proper numeric sorting.

 

If neither of those are a possibility, you could also try using the LPAD function to automatically append 0's to any numbers that are smaller. For example:

ORDER BY LPAD(`colName`, 4, '0') ASC

Where 4 is the number of digits to go to, e.g. 0001

 

That is definitely not the best solution however, as it will have an impact in the speed of the SQL queries, reducing database overhead is generally the better way