[RESOLVED] User Rankings

digital_skwerl

I have 2 tables.

Table 1 - user account info

# Table "users" DDL

CREATE TABLE users (
user_id int(5) NOT NULL auto_increment,
user_type varchar(20) NOT NULL default 'City Dweller',
first_name varchar(50) NOT NULL default '',
last_name varchar(50) NOT NULL default '',
business varchar(100) NOT NULL default '',
email varchar(40) NOT NULL default '',
address1 varchar(30) NOT NULL default '',
address2 varchar(30) NOT NULL default '',
city varchar(30) NOT NULL default '',
state char(2) NOT NULL default '',
zip varchar(5) NOT NULL default '',
county varchar(255) NOT NULL default '',
mls varchar(100) NOT NULL default '',
newsletter char(1) NOT NULL default 'N',
date datetime NOT NULL default '0000-00-00 00:00:00',
username varchar(20) NOT NULL default '',
password varchar(255) NOT NULL default '',
thumb varchar(255) NOT NULL default 'images/person3.png',
pic varchar(255) NOT NULL default 'images/person2.png',
photo varchar(255) NOT NULL default 'images/person1.png',
thumb1 varchar(255) NOT NULL default 'images/person3.png',
photo1 varchar(255) NOT NULL default 'images/person1.png',
thumb2 varchar(255) NOT NULL default 'images/person3.png',
photo2 varchar(255) NOT NULL default 'images/person1.png',
thumb3 varchar(255) NOT NULL default 'images/person3.png',
photo3 varchar(255) NOT NULL default 'images/person1.png',
thumb4 varchar(255) NOT NULL default 'images/person3.png',
photo4 varchar(255) NOT NULL default 'images/person1.png',
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table 2 - user activities with points value

# Table "bricks" DDL

CREATE TABLE bricks (
brick_id int(10) NOT NULL auto_increment,
user_id int(8) NOT NULL default '0',
brick_type varchar(255) NOT NULL default '',
brick_num int(11) NOT NULL default '0',
brick_date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (brick_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Each time a user performs a specific activity a new row is created in the "Bricks" table saving the user_id, the activity, and a point value for the activity.

I can get the number of points for User1 by:

$query1 = "SELECT SUM(brick_num) FROM bricks WHERE user_id='1'";

I now want to show User1 that they are ranked X out of XX total users.
What is the best way for me to select the sum of bricks for all other users and count the number of rows where the sum of bricks is less than the sum for User1?

Seems like GROUP BY might help but Im just not clear how to handle the grouped data.

Thanks

bretticus

Honestly, I can't think of a way to do this in MySQL off the top of my head. However, if you want a non-elegant (and possibly resource intensive) solution you could order by "...SUM(brick_num) DESC..." and loop through the results incrementing a counter until the userid matches up with the current user. Then break the loop. The counter is the user's rank.

That being said, this is an intriguing exercise, I might be able to come up with something more feasible if I really put my brain into it (something I'm unwilling to do at the moment.) Anybody else up for the challenge?

digital_skwerl

bretticus wrote:
you could order by "...SUM(brick_num) DESC..." and loop through the results incrementing a counter until the userid matches up with the current user

That's a good idea. Ive been doing similar stuff but it all just seems really sloppy. Sometimes it just feels like there's a better way to do things and I cant find it 😕.

I'll follow up.
Still open to suggestions though.

rkolbe

Here is a nice little tid bit that will help you out....sub queries is what you really need here...You might even want to consider the structure of your tables:

http://mysqldatabaseadministration.blogspot.com/2006/03/scaled-ranking.html

digital_skwerl

Thanks for the help guys, turns out I just needed to use a join (something I've never had too good of a grasp of). Found it here...
http://www.1keydata.com/sql/sqljoins.html

Still using a counter, and I think I can clean it up a bit but it works so BOOYEA.

Here it is.

$uid = $_SESSION['user_id'];
$query4 = "SELECT users.user_id AS user, SUM(bricks.brick_num) AS brick 
FROM users, bricks WHERE users.user_id = bricks.user_id 
GROUP BY users.user_id ORDER BY brick DESC";
$result4 = @mysql_query($query4);
if ($result4) {
$count = 0;
while ($row4 = @mysql_fetch_array($result4, MYSQL_NUM)) {
$count = $count + 1;
$user_id = $row4[0];
if ($user_id == $uid) {
$rank = $count;
}
}
echo "$rank <br>";

Thanks for the suggestions. Hope someone finds this helpful.