SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello there,

    How's it going today?

    Does anybody please know how to sort records in a MySQL table by character size.

    i.e. Select * FROM table ORDER BY .....e.t.c (something like this?)

    e.g. of table now:
    1. apples (6 characters)
    2. oranges (7 characters)
    3. pears (5 characters)

    e.g. of how I'd like the data to be retrieved from the table with the smallest sized words listed first.
    1. pears
    2. apples
    3. oranges

    I hope this makes sense? I'd be grateful for any help.

    Thank you,

    Regards,

    Jason

  2. #2
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this :

    SELECT *, LENGTH(fruit) as size FROM table ORDER BY size

    Change 'fruit' to whatever your field name is.

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2000
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much oodie.....

    It works fine!!!

  4. #4
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if we need to find a record with such length, I tried WHERE LENGTH(var)=10 and it said syntax error.

    Another question: how could we sort the records after adding them? Since when I tried to access them, it seems that which added first will display first. And I need to sort in another order. How could I do that? (I don't mean sorting in selecting, but the way the data is stored)

    Thanks

    [Edited by 123finder.com on 10-05-2000 at 11:38 PM]
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  5. #5
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by 123finder.com
    What if we need to find a record with such length, I tried WHERE LENGTH(var)=10 and it said syntax error.
    Can you post your query?
    I tried this query and it worked :
    SELECT * FROM tbl WHERE LENGTH(field)=10

    Make sure there isn't any unclosed quotes, etc in the query.


    Another question: how could we sort the records after adding them? Since when I tried to access them, it seems that which added first will display first. And I need to sort in another order. How could I do that? (I don't mean sorting in selecting, but the way the data is stored)
    I don't think that you can change the way the data is stored in the database. But if I'm not wrong I think what you mean is you want to sort it for admin purpose (like in phpMyAdmin). In that case you have to change the script manually.

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Indeed, you should never rely on the order of storage of your database data. Whenever you access this, you are using a SELECT, so ordering should always be done using an ORDER BY clause in that query.

    -Kev.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •