MySQL Question

[moondog]

I ran into a strange thing that I was not expecting today.

I am trying to query from a database table and order by a column. This is not normally a big deal. The column (names "TLD") on which I am sorting is an ENUM and is defined like this:

enum('com', 'net', 'org', 'us', 'info', 'biz')

The select statement I have looks like your garden variety select statement:

$sql = "SELECT * from DOMAINS order by TLD asc";

I expected the results to be given to me in this order:

biz
com
info
net
org
us

This is not the case. The results are displayed in the order that they appear in the enumeration. In other words, they display in this order:

com
net
org
us
info
biz

Other than the obvious solution of putting the values in the enumeration in the correct order - Is there a way to sort the rows in the enumerated column alphabetically instead of the way that they appear in the enumeration?

-Bob

 

[dongan]

then try enum('biz', 'com', 'info', 'net', 'org', 'us')

 

[Kate]

It's a known issue but interesting as it might happen to us
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/ENUM.html

 

[Xyzer]

then try enum('biz', 'com', 'info', 'net', 'org', 'us')

Thats what i said but i cant see my post :s

 

[moondog]

It's a known issue but interesting as it might happen to us
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/ENUM.html

Thanks Kate! Guess I need to rethink a little bit.

then try enum('biz', 'com', 'info', 'net', 'org', 'us')

Thats what i said but i cant see my post :s

Thanks guys, but my original post said

"Other than the obvious solution of putting the values in the enumeration in the correct order - Is there a way to sort the rows in the enumerated column alphabetically instead of the way that they appear in the enumeration?"

-Bob

 

[Kate]

Quick fix:cast the enum value into a regular string

$sql = "SELECT * from DOMAINS order by concat(TLD, '' ) asc";

 

[moondog]

Quick fix:cast the enum value into a regular string

$sql = "SELECT * from DOMAINS order by concat(TLD, '' ) asc";

SWEET!

Works perfectly.

-Bob