SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    adding multiple records

    Hi. I am trying to make a 'points' system for my membership site.

    There is a user table. A ratings table. A favorite table and a comments table. They are all linked to the user table by storing the users id.

    I am trying to list the top users. As in, the users with the most ratings, favorites, and comments.


    This is what i have so far, although i dont entirely know what im doing, can someone help?

    PHP Code:
    $query mysql_query("SELECT COUNT(distinct ratings.id) AS ratingcount, COUNT(distinct comments.id) AS commentcount, COUNT(distinct favorites.id) AS favoritecount, users.userid, users.username FROM ratings, comments, favorites, users GROUP BY user.id DESC LIMIT 5"); 
    so just to summarize, i need to list the top 5 users who have the most ratings, comments and favorites, then display there username and the total points they have.
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What defines a point? I'm guessing the sum a users ratings + comments + favorites? So you want to list 5 users, not 15?

    In any case, this is best solved primarily in the sql query, not in php. The mysql forum would have been more appropriate.

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a add function in mysql for adding .I think it works as following:

    Code MySQL:
    Select sum(blahcolumn) from blahtable;

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well sum ads all the values.. but i just want to count the number of records associated with a user id.

    im trying to list the 5 users who have the most ratings, comments, favorites.. anyone know?
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    something like this...
    Code:
    SELECT ratingcount + commentcount + favoritecount
             AS totalcount
         , userid
         , username
      FROM (
           SELECT ( SELECT COUNT(*) 
                      FROM ratings
                     WHERE userid = users.id 
                  ) AS ratingcount
                , ( SELECT COUNT(*) 
                      FROM comments
                     WHERE userid = users.id 
                  ) AS commentcount
                , ( SELECT COUNT(*) 
                      FROM favorites
                     WHERE userid = users.id 
                  ) AS favoritecount
                , users.userid
                , users.username 
             FROM users 
           ) AS u
    ORDER
        BY totalcount DESC LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Thank you for taking the time to reply. That makes a lot of sense to me. I implemented it, only problem is, it doesn't seem to load? like the query wont complete on my server, its like it cant handle it..

    do i need to change anything about the tables? add an index or something?
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its like the query is timing out.. which is weird because there are only a few thousand entries in each table
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you have any indexes on those tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the comments table:
    Keyname Type Cardinality Action Field
    PRIMARY PRIMARY 532 id

    For the favorites table:
    Keyname Type Cardinality Action Field
    PRIMARY PRIMARY 6625 id

    For the ratings table:
    Keyname Type Cardinality Field
    PRIMARY PRIMARY 4629 id
    contentid INDEX 2314 contentid
    rating INDEX 24 rating

    For the users table:
    PRIMARY PRIMARY 5906 userid

    Sorry for my ignorance but i dont even know what the index does really. Maybe i shouldnt haev those indexes in the ratings table?

    Here is the SQL query im using, i adjusted the table names slightly:
    PHP Code:
    SELECT ratingcount commentcount favoritecount
             
    AS totalcount
         
    userid
         
    username
      FROM 
    (
           
    SELECT SELECT COUNT(*) 
                      
    FROM ratings
                     WHERE memberid 
    users.userid 
                  
    ) AS ratingcount
                
    , ( SELECT COUNT(*) 
                      
    FROM comments
                     WHERE memberid 
    users.userid 
                  
    ) AS commentcount
                
    , ( SELECT COUNT(*) 
                      
    FROM favorites
                     WHERE memberid 
    users.userid 
                  
    ) AS favoritecount
                
    users.userid
                
    users.username 
             FROM users 
           
    ) AS u
    ORDER
        BY totalcount DESC LIMIT 5 
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you ever though how google is able to search so quick? Answer is that they have a very good indexind system. so what is index?
    Let say on your website you have a search engine which allows you to search for other user name. If you donot have an index then under heavy load your searchengine will faile because search engine will try to iterate each and every user tuple trying to find the match.Index is like table od content, it just go through the table and find the user tuple it needs.

    I hope I am correct.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    each of the other 3 tables should have an index on memberid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    USA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome.. it works now!! Thank you so much.. im gonna have to do some homework on "indexes" i had no idea they were important.. heh..
    http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC


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
  •