SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by number of votes

    Im trying to retrieve items from my database ordering them by which one has the highest votes... it structured like this

    id - title - votes
    1- something - 3213, 34234, 65436, 23, 345, 54532
    2- other - 342,
    3- title - 123, 3562, 642
    4- etc - 0
    5- etc - 432424, 34243

    the random numbers would stand for user id, and everytime a user votes, his id simply gets added to the field, seperated by a comma

    so basicly i figure i would have to count the number of commas, and see which one has the most... making the order
    1- something - 3213, 34234, 65436, 23, 345, 54532,
    3- title - 123, 3562, 642,
    5- etc - 432424, 34243,
    2- other - 342,
    4- etc - 0


    also, how would i got about checking if the user already voted?

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by franco714 View Post
    ...and everytime a user votes, his id simply gets added to the field, seperated by a comma
    i urge you most strenuously to change this design

    you are asking for a world of hurt -- complex sql and performance worse than cold glue

    what you want is a table with one row per user vote

    then the sql becomes practically trivial and can easily be optimized

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •