SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Non-Member
    Join Date
    Nov 2004
    Location
    Ithaca, NY
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Little puzzle with ENUM / ORDER BY

    I'm not sure how to do this.

    Basically, I'm looking for a way to specify the sort order of an enum column in my query, if possible.

    Specifically, I want to order my results (data rows about a picture of an insect) by the value of an enum field (view angle: top, bottom, side, null). I have different categories of insects that I want to sort in different orders based on a kind of precedent in books on my subject. Larva tend to be shown from the top and adults from the side in most cases. So I want the top view to be the first on the page for larva, and the side view to be the first on the page for adults, and so on. There will be a few different cases like this.

    Is there any way to do this with my MYSQL query, or am I going to have to hack out a solution with PHP?

  2. #2
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found this on the mySQL enum page:

    ENUM values are sorted according to the order in which the enumeration members were listed in the column specification
    So they probably should be ordered by the order that you typed in the ENUM array at creating the table.

    I'm not sure this is what you wanted, but it's all I could interpret

    EDIT:

    Ok ok, I've read it again and I think I understand what you're asking. No, it isn't possible to specify a sort order at run-time. All I can think is to alter the table with each query, but I don't know how fast that would be. PHP hack seems like the only choice, IMO.
    michael.Crabbe

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can order any column, including an enum column the following way, of course you will have to create three different order by if you want the top view first in some cases, the side view first another time and the bottom view first another time.

    Code:
          SELECT foo, bar, bugview
          FROM bugtable
          ORDER BY FIELD(bugview,'top','side','bottom')
    This will show the bugview column in exactly that sorted order.

    EDIT: To allow for a null value in your enum column, you merely have to add a CASE statement:

    Code:
       SELECT foo, bar, bugview
       FROM bugtable
       ORDER BY CASE WHEN bugview IS NULL then 1 ELSE 0 end,
        FIELD(bugview,'top','side','bottom')
    If you wanted to get really daring you could write the order by in a bunch of different CASE statements depending on your chosen category but then you'd have to do this in PHP to pass the variable along holding category value. Still you'd only have one long mysql query which would be much faster than looping it through a PHP loop.


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
  •