SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Crazy MySQL conundrum (to me at least)

    Hi there,

    I have 16 participants. Each participant has it's own "row" in the database, including the columns place1, place2, place3, ..., all the way to place16. These "place" columns in the rows indiciate how many times a participant has been voted for that respective place. For example, if place10 column = "1337" for a row, that particular participate was voted 1337 times to win 10th place in this competition.

    Now the hard part is syndicating all of these data into meaningful results.

    What I am trying to do is: how can I display a list of results for all 16 people where the most votes for place1 is put in the first row of the result set, the most votes for place2 is put in the second row of the result set, the most votes for place3 is put in the third row of the result set, etc... is this possible? I don't want to do this with 16 queries to find out...

    Any help is mightily appreciated. Thank you in advanced.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it would be ~so~ much easier if you could split 16 place columns off into 16 rows of a related one-to-many table (this is called first normal form, by the way)

    both participant_id and place_number would form the composite primary key for this second table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since the system has already been built, it would be mighty difficult to change the database structure and code that is integrated with it.

    Are there any ways around this?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, there is, but it's far too ugly and complex for me, sorry

    could you extract the data into separate throwaway-after-the-report-is-done tables?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Panduola View Post
    Since the system has already been built, it would be mighty difficult to change
    If I had a dollar for every time I heard/read this I swear Bill Gates would be looking in his rear view mirror and I'd be coming along nicely!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, it's sad

    not picking on this particular example, but how does a system for data storage get built ~before~ thought is given to how the data will be extracted?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The schema is an obvious deviation from proper design principles. Its not even about extracting data but lack of education/understanding.
    The only code I hate more than my own is everyone else's.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Second "best" solution in this case : extract all data and elaborate it with PHP (or whatever language you're using).

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, I haven't had my database courses in school just yet...

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doesn't prevent you from looking up database normalization though does it? :-)


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
  •