How to make the function "Order albums alphabetically" work different?

[AvrilBoi]

I wanted my gallery to order my albums alphabetically, so I've searched on the forum and found that I just have to replace in index.php this:

Code Select Expand

    $sql = 'SELECT a.aid, a.title, a.description, category, visibility, filepath, ' . 'filename, url_prefix, pwidth, pheight ' . 'FROM ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON a.thumb=p.pid ' . 'WHERE category=' . $cat . $album_filter . ' ORDER BY a.pos ' . $limit;
with this:

Code Select Expand

    $sql = 'SELECT a.aid, a.title, a.description, category, visibility, filepath, ' . 'filename, url_prefix, pwidth, pheight ' . 'FROM ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON a.thumb=p.pid ' . 'WHERE category=' . $cat . $album_filter . ' ORDER BY a.title ' . $limit;
And it does work.
But the correct order for alphanumeric characters is (for example) 100 before 11, so if I have an album called 11, I'll see it after the one called 100.
I know that I could modify the names of the albums, but I have too many albums, and by the way it is not too good to view "0100", I'd prefer to view "100"...
So I'm asking if there is a solution for changing the alphabetical album ordering function, so that (for example) makes 11 come before 100, and not after. (Like it is happening right now)
A thing like this but made for thumbnails' alphabetical ordering has been done before.
I hope you can help me, thanks.

[Nibbler]

Try

Code Select Expand

$sql = 'SELECT a.aid, a.title, a.description, category, visibility, filepath, ' . 'filename, url_prefix, pwidth, pheight ' . 'FROM ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON a.thumb=p.pid ' . 'WHERE category=' . $cat . $album_filter . ' ORDER BY CAST(a.title AS INTEGER) ' . $limit;

[AvrilBoi]

When I try to view an album I get a mysql error:
While executing query "SELECT a.aid, a.title, a.description, category, visibility, filepath, filename, url_prefix, pwidth, pheight FROM cpg146_albums as a LEFT JOIN cpg146_pictures as p ON a.thumb=p.pid WHERE category=4 ORDER BY CAST(a.title AS INTEGER) LIMIT 24,11" on 0

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER) LIMIT 24,11' at line 1

[AvrilBoi]

Oh, If it is useful... my version of mysql is 4.1.20

[AvrilBoi]

Maybe I should use this:
ORDER BY CAST(SUBSTRING_INDEX(`a.title`, \'.\', 1) AS SIGNED) ASC
insted of order by a.title ?
I tried but there is this error:
mySQL error: Unknown column 'a.title' in 'order clause'

[AvrilBoi]

Since my albums which contain numbers are called *#NUMBER
I've solved with this:
order by substring_index(a.title,\'#\',1), cast(substring_index(a.title,\'#\',-1) as unsigned)
But this doesn't work if the album is called * NUMBER... it only works if there is # before the number... how can I make it work always?