-1

I am changing a php plugin and having a hard time to get the sorting of the array it pulls from the database right. As of now I have it like:

order by LENGTH(name), name

Which gets me close but not right. It outputs like so:

8.2-11 8.2-12 8.2-13 8.2-14 8.2-13-A

Which I understand because 8.2-13-A is longer than 8.2-13 so it will be at the end. How can I ignore the -A or -B so the two 8.2-13's are right after each other?

Packy
  • 3,405
  • 9
  • 50
  • 87
  • 1
    Can't you just use `order by name`? – Markus Safar Nov 24 '15 at 20:57
  • If you want to order by the value itself, why are you trying to order by its length? – David Nov 24 '15 at 20:58
  • `order by LENGTH(name), name` that isn't PHP, it's SQL; 2 different animals here. Where does the PHP part come in? *"I am changing a php plugin"* – Funk Forty Niner Nov 24 '15 at 20:58
  • @David Order by name outputs non natural order so it would go `8.2-1, 8.2-10, 8.2-2, 8.2-20`. Thats why I had length in there – Packy Nov 24 '15 at 21:01
  • @Packy: That's a natural order for string values. Which these are. – David Nov 24 '15 at 21:03
  • @David sorry, I thought natural order was `8.2-1, 8.2-2, 8.2-10, 8.2-20` which is what I am looking for. – Packy Nov 24 '15 at 21:04
  • I guess "skipping" everything from `-` would be more complex in SQL than in PHP... Must the sorting be done in the database directly? – Markus Safar Nov 24 '15 at 21:05
  • 1
    @Packy: Then you need to use data which can be sorted by that. Each of these elements contains two separately sort-able values. A decimal value before the hyphen and an integer value after the hyphen. You need to separate this string into the components by which you want to sort. Storing multiple values in a single column makes this unnecessarily difficult. – David Nov 24 '15 at 21:06
  • @MarkusSafar, this is just for the front end. Basically outputting the products on a page, organized by the `name` – Packy Nov 24 '15 at 21:09
  • @Packy I've posted an answer but I could not test it here... It should hint you in the right direction though... – Markus Safar Nov 24 '15 at 21:14

1 Answers1

0

You could try to find the last occurance of the - sign by using something like: LENGTH(name) - LOCATE("-", REVERSE(name))

With this information it should be possible to only use a substring of the original string like: ORDER BY LENGTH(SUBSTRING(name, (LENGTH(name) - LOCATE("-", REVERSE(name))))), SUBSTRING(name, (LENGTH(name) - LOCATE("-", REVERSE(name))))

BUT: I'm pretty sure this could have severe influence on the performance. Maybe it's a better idea to implement a solution with a stored procedure or do this directly in php.

Markus Safar
  • 6,324
  • 5
  • 28
  • 44