SQL question

Discussion in 'OT Technology' started by babygodzilla, Mar 7, 2007.

  1. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    this is probably a simple question. so i have all 52 states in my database, ordered by alphabet and state_id. so Alabama's state_id == 1, and California is 5.

    i make drop downs base on this database. when i pull data from the table i order by state_id, so Alabama comes up first (Alabama is SELECTED). what if I just want California to come up first, without actually hard-coding it to anything. i wanna be able to do it smartly. ideas?

    thanks!!
     
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    You want California only, or California and then the normal order?

    if you want California only, then add WHERE state_id = 'California'

    If you want California and then the other states ordered normally behind it, I would do a simple deterministic function called say CALI_FIRST that returns 0 for California, or the state number for any other state, then do: ORDER BY CALI_FIRST(state_id)

    You could order by a CASE() too, where it selects 0 if it is California, or the state_id otherwise.
     
  3. ez4me2c3d

    ez4me2c3d Cold Member

    Joined:
    Sep 19, 2003
    Messages:
    1,836
    Likes Received:
    0
    Location:
    Minneapolis, MN
    Anyway you do it... it's going to be hard coded for Cali. There's no work around for this. You are specifically targeting a value out of a range of values, and a match has to occur.

    In your code to create the [select] element with it's [option] elements, try this
    Code:
    $selected = '';
    $target = 'California';
    if ($value == $target) {
      $selected = ' selected="selected"';
    }
    echo '<option value="' . $state_id . '"' . $selected . '>' . $state_name . '</option>';
    This will have all states in the drop down, sorted by Alpha, but the default selected item will be Cali.

    Note that it will not be at the top of the list, but in a sense the list will be scrolled to the position Cali is in, in the list.
     
  4. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Thats definitely a better solution.
     
  5. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    this is what i want. hmmm... maybe what's even better is to allow users to choose their own default state. i could save that option in the database, based on the state_id, but that would be complicated wouldnt it...
     
    Last edited: Mar 7, 2007
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Its easy as hell for me, but in PHP... prolly complicated. And certainly ugly.
     
  7. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    store their state. Then load the contents in ascending order into the combo box. Then using a simple "if" statement compare the state's id to the users default; if they match, add the "selected" attribute to the item so it's selected. The list is still alphabetical, but now their state is the one that is selected.
     
  8. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    And be sure to put lots of logic in with the view, so that it is butt ugly and impossible to maintain.
     
  9. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    its ONE if statement, hardly ulgy :ugh:
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    52 States?
     
  11. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    not rly states, but I assumed he meant Puerto Rico and Virgin Islands.
     
  12. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    Add a new field to the table called Order. You can use this to override a state's order. Then, you return the states ordered first by the Order field, then by the state_id. i.e. ORDER BY `Order`,`state_id`

    Please note the backticks on Order! ORDER is a SQL keyword as you know so if you don't escape the column name you'll have problems.

    I do this ALL the time - I might make such a table, and the client will often want to change the order. Having an Order field like this which allows custom ordering saves tonnes of time, especially if you put it in there first thing.

    The advantage to this is you can maintain the Order of the data with the data itself, rather than in business logic.

    Make Order something big like 520 by default, so then by default, all states will return in state_id order.

    Now if you want to change the order of the states, you can change their Order id to bump them up.

    The reason I say choose 520, is that gives you heaps of breathing room to give states numbers and be able to change them without having to renumber everything again.

    i.e. Make Cali come up first on the list by making it have an Order of 10. If you want to bump something before Cali, you can number that 5 without having to renumber Cali. If you wanted to initially number everything, number the states from 1 to 52 in increments of 10 (Alabama = 10, whatever's next = 20 and so on).

    Hope all this waffle made sense, I tend to go on too long trying to explain something!
     
  13. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    I'm certainly no expert but I thought it was considered extremely bad form to physically order your data in tables. In my DB class, the prof taught that tables are the same as sets and when dealing with sets, order is irrelevent. In fact, he would count off points if we used something to order the data within the tables.
     
  14. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    If you don't order in your queries, you can't accomplish much. As to how the database system orders things... not my concern. Its good at that.
     
  15. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0

    actually, peyomp, im not sure how this goes. where would this function go? care to write an example? thanks
     
  16. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0

    yes sir.
     
  17. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Yes, but proper DB practice also says you MUST use a Primary Key. And a primary key orders the items in the order that they were added to the DB. So did he deduct points for following proper schema design?
     
  18. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Whops....I should have clarified.

    Here's how I was taught. Primary keys were explained to be different than ordering. They are simply unique identifiers. They may very well order the table, if the tuples were inserted in the order but this cannot be relied upon. Further, one should never change primary keys in order to make the tuples in the table ordered.

    And because queries can be ordered, there isn't usually a reason to store data in a table in any sort of order, nor add a field that would order the data. He did say that there were times when one would want to store ordered data in a table but this table should be only used to store query results....not hold actual data records.

    Peyomp, ordering queries is not the same issue. I'm referring to the actual tuples in the table.

    Given that this is an University environment it may be an ideal situation. Is r/l DB practices different??
     
  19. YodaHart

    YodaHart New Member

    Joined:
    Jul 7, 2006
    Messages:
    605
    Likes Received:
    0
    You don't need a function to display california first, just use a modified SQL query:

    Code:
    SELECT state_name, state_id
       FROM (SELECT state_name, state_id, '1' "DISPLAY_ORDER"
                   FROM ALL_STATES
                  WHERE STATE_ID = 5
                UNION
                SELECT state_name, state_id, '2'
                   FROM ALL_STATES
                 WHERE STATE_ID != 5) SUBQ
      ORDER BY DISPLAY_ORDER, state_name
    
    ...Or you can just change the state_id for california to 0 and order by state_id
     
  20. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    well, as an example, I have applications that need to list products within categories in order of importance. For example:

    Product XYZ
    Product ABC
    Product CB2
    Product BFZ

    How would you query that without a "priority" column in the table?

    I agree that data should be unordered whenever possible... But sometimes it's the only way to efficiently accomplish certain results. So to answer your question, yes and no. "Ideal" practice is still the same. But r/l sometimes requires you to bend the rules to get things done. Education is always gonna be black/white. Right/Wrong. Once you get into the world, you're gonna see a lot of grey. That's why education has it's place -- it teaches you good theory and practice; however, real-world experience is also a very important component.
     
  21. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Yeah since you mention an order of importance, this implies some ranking mechanism that you would need to capture so yeah, I can see how you would want to store that data in the tables.
    yeah...I thought that might be the way it is.

    It's kinda funny because I worked for 10 years between degrees and I've seen how the real world works so sometimes I just laugh when professors talk about right and wrong.

    edit: damn...my grammar sux today
     
  22. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    SUBSTR, if I had to. But then if I do that a lot they should be in separate fields.
     
  23. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    Thanks all for the great input. I have another question now. Is anyone familiar with the Class and Method classes in Java?

    I don't wanna explain the whole problem, cause it gets pretty complicated. So long story short, I have a method that receives two parameters, among others, className and methodName. Using these two pieces of information, the next part of the app will call the method methodName in the class className (so call method className.methodName(); ).

    What I did was this

    Code:
    Class klass = forName(className);
    Class[] args = [arguments for the method methodName]
    Method method = klass.invoke(methodName, args);
    
    at least i THINK it's invoke(), i forgot i dont have the file in front of my eyes. in any case, this code works, EXCEPT forName() wants the FULL PATH of the class, including the package and all that, otherwise it'll return a class not found error. So the correct className, for example, will be, com.offtopic.awe.Some

    my question is. is there a way around this? of course this makes sense, because the method that's calling all this shit may or may not be in the same package as com.offtopic.awe.Some. but package names tend to get veeeerryy long, and it's very very annoying, troublesome, not to mention typo-prone. suggestions? thanks!
     
  24. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Can't you just import the package containing the class you want?? Something like:

    import packageName.className;

    or perhaps

    import packageName.*;
     
  25. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    I think you're misinterpreting your Professor wrong (either that or he's bad).

    Yes, he's right if the table has the data you want to sort by. If you want to sort by name, sure, do that with the SORT BY clause.

    However, if the data needs to be sorted in an order not determined by any data in existing columns, you need a custom order/priority (whatever you want to call it) column. This is far better practice than fetching that data and then sorting it manually with code, rather than letting the SQL server do it in a nice clean query.

    It also makes it easy for you to change the order (and also allows your client to maintain the custom order themselves in your admin interface, rather than you having to change your code if the order is to be changed).
     

Share This Page