adding multiple records

[moxsum]

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?

$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.

[crmalibu]

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.

[uniqueumang]

There is a add function in mysql for adding .I think it works as following:

Select sum(blahcolumn) from blahtable;

[moxsum]

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?

[r937]

something like this...

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

[moxsum]

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?

[moxsum]

its like the query is timing out.. which is weird because there are only a few thousand entries in each table

[r937]

do you have any indexes on those tables?

[moxsum]

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:

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 

[uniqueumang]

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.

[r937]

each of the other 3 tables should have an index on memberid

[moxsum]

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..