Skip to main content
C
christopherkowalski
Inspiring
May 25, 2006
Question

Question About Looping Over A List

  • May 25, 2006
  • 2 replies
  • 223 views

In my application I have a table that stores information about products. One column in that table, called "options", stores a list of numbers like "1,3,2,4". In some cases the numbers may not be in numerical order, for which I have reasons. Each of these numbers represents the indexed row from another table, "sizes", where I store a list of the sizes and information about each.

On the page displaying a product to visitors I pull data from the "options" field and loop over it. Inside the loop I retrieve the corresponding row from the "sizes" table, and it is added as an order option in a pull-down menu. For example, "1,3,2,4" is:

(1) Small
(3) Large
(2) Medium
(4) X-Large

I want to be able to order the results alphabetically, rather than numerically or the order they are in my "options" column, like:

(3) Large
(2) Medium
(1) Small
(4) X-Large

Is there any easy way to reorder these? I thought of having my application re-order them when they are initially set up, but in some cases the names of each size may change after being set. Clearly having it re-order them on output is the best solution.
    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    May 26, 2006
    Hire somebody to normalize your database.
    Learn what they did and why.
    P
    paross1
    Participating Frequently
    May 25, 2006
    I know that I am starting to sound like a broken record, but having a database column, in which you have a list of numbers as options, is a very bad design and should be reconsidered, if possible. What you probably should have is something like a productSize table where you have a productID and size field, so that you would have a row for every productID and size value pair. You would remove the option field altogether from the product table. Then you could join product to productSize to get the list of sizes, and if you also joined to the Size table (where I assume that you would have size and description fields), you could sort on any of the fields desired, like Size or description, etc.

    Something like this, perhaps?

    SELECT p.productID, p.product_Description, s.size, s.description
    FROM product p
    INNER JOIN productSize ps ON ps.productID = p.productID
    INNER JOIN Size s ON s.size = ps.size
    ORDER BY s.description

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices