[Typo3-dev] Sorting without "ORDER BY sorting"

[Jeroen Serpieters]

Mathias Schreiber [wmdb>] wrote:
> Hi guys,
> 
> I need to sort extension tables without being able to use "sorting".
> so basically I select and ORDER BY title.
> But I have several DVD series and this results in something like this:
> 
> Godzilla 1
> Godzilla 10
> Godzilla 2
> ...
> 
> Any idea, how I can sort thing correctly?
> 

Unfortunately mysql doesn't have a natural order.
What you could do is seperate the text and the numbers, then just order 
by the text and the number you stripped off.

If the field containing this field is always formed on the same way, 
being "string [space] number"
Then you can use the substring_index() function to split it into a 
string and a number.

Example:
select * from dvds order by substring_index(title, ' ', 1), 
substring_index(title, ' ', -1) * 1

But the easiest, especially if the field isn't always formed like stated 
before, is retrieving all data and let php do the sorting with natcasesort()


-- 
Jeroen