SitePoint Sponsor |
|
User Tag List
Results 1 to 12 of 12
Thread: adding multiple records
-
Feb 24, 2009, 15:22 #1
- 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");
http://www.mmatalk.com <- Talk Mixed Martial Arts / Pride / UFC
-
Feb 24, 2009, 15:40 #2
- 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.
-
Feb 24, 2009, 18:25 #3
- Join Date
- Nov 2007
- Posts
- 63
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Umang Parikh
http://www.technotrack.co.nz/
-
Feb 26, 2009, 05:36 #4
- 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
-
Feb 26, 2009, 06:33 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Feb 26, 2009, 17:10 #6
- 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
-
Feb 26, 2009, 17:14 #7
- 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
-
Feb 26, 2009, 17:56 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
do you have any indexes on those tables?
-
Feb 26, 2009, 18:17 #9
- 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
-
Feb 26, 2009, 18:37 #10
- 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.Umang Parikh
http://www.technotrack.co.nz/
-
Feb 26, 2009, 20:22 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
each of the other 3 tables should have an index on memberid
-
Feb 27, 2009, 00:00 #12
- 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