Page 1 of 1

ORDER BY length(field) ?

Posted: Sun Jun 06, 2004 10:01 pm
by The Monkey
How would I do a mysql_query that ordered by the length of text in a particular field?

I have a submenu script that displays all the pages in a directory of my website, however, for stylistic reasons, I want it to be ordered by the length of the title for each page on the submenu.

Any 'relatively simple' code that would work?

- Monkey

Posted: Sun Jun 06, 2004 10:09 pm
by Illusionist
i'm pretty sure ORDER BY LENGTH(field) will work.

Posted: Sun Jun 06, 2004 10:14 pm
by Illusionist
Or it might be ORDER BY LEN(field)

Posted: Sun Jun 06, 2004 10:17 pm
by The Monkey
I'm not getting LENGTH(field) to work.

Could someone give me the whole $query var?

Thank you.

Posted: Sun Jun 06, 2004 11:54 pm
by bla5e
ORDER BY <feild> LENGTH

Posted: Sun Jun 06, 2004 11:55 pm
by bla5e
$sql = "SELECT * FROM <table> ORDER BY <feild> LENGTH";

Posted: Mon Jun 07, 2004 2:04 am
by The Monkey
bla5e wrote:$sql = "SELECT * FROM <table> ORDER BY <feild> LENGTH";
That code does not work.

Posted: Mon Jun 07, 2004 2:15 am
by feyd
"SELECT * FROM `test` ORDER BY LENGTH( `something` )"

works perfectly for me...

Posted: Mon Jun 07, 2004 6:25 am
by launchcode
As Feyd said, LENGTH() will work just fine - if you're using multi-byte characters (i.e. a foreign language character set) then use CHAR_LENGTH() instead, as LENGTH gives you an incorrect reading in this instance.

Posted: Tue Jun 08, 2004 5:21 pm
by The Monkey
Ok: It works now.

Thank you. Now I must figure how to deal with the i character being narrower than the o character... it's messing up the way I WANT my site to work... :(

Posted: Tue Jun 08, 2004 6:22 pm
by launchcode
Welcome to non-proportional fonts. Try using PRE tags if you absolutely must have fixed-width characters (or set the font to be something like Courier or Terminal but realise you'll exclude Mac users, etc).

Posted: Tue Jun 08, 2004 7:35 pm
by The Monkey
Ah... ok.

I have a sorta-solution. In the db, I put ? after the titles to "jack" up the character length. Then, after Ordering By the length of characters, I strip out the ?.

It doesn't always work, but it does make it look a LOT better, and by golly it's better than nothing!