ranking counts

[davidcriniti]

Hi,

 

I've got a site where teachers can upload resources to share amongst staff and students. In order to motivate staff to share resources, I'm doing things like introducing badges and ranks etc.

 

My latest bit of script counts the amount of uploads a staff member has.

 

I can also find the total number of staff who've uploaded, but how I'm not sure how to find the current user's ( ie: Where members.member_id = ". $_SESSION['member_id']  )   rank out of that total in terms of the amount of uploads they have.

 

Any advice would be appreciated. Script so far is :

 $total_uploads = mysql_query("SELECT members.member_id, members.member_firstname, members.member_lastname, COUNT(*)  
FROM uploads    
JOIN members
ON uploads.member_id = members.member_id
GROUP BY uploads.member_id 	 ");

$total_uploads_count	= @mysql_num_rows($total_uploads);
	 	 
$upload_count = mysql_query("SELECT members.member_id, members.member_firstname, members.member_lastname, COUNT(*)  
FROM uploads    
JOIN members
ON uploads.member_id = members.member_id AND members.member_id = ". $_SESSION['member_id']  . "
GROUP BY uploads.member_id 	 ");
	 
while($row_count=mysql_fetch_array($upload_count))  
{  
echo "<tr>";  
echo "<td align='right'> Your upload count: </td>";  
echo "<td align='left'>" . $row_count['COUNT(*)'] . " (Your rank is __ out of $total_uploads_count </td>";  
echo "</tr>";  
}  
echo "</table>";  	 

[Q695]

http://php.net/manual/en/function.mysql-num-rows.php

<?php

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

?>

[davidcriniti]

Hi Q695,

 

That is telling me the total number of rows, which my query already does.

 

The part I'm needing help with iis to find the user's rank by comparing the amount of uploads a user has to the amount of uploads all other users have. Ie: User with most uploads would be ranked #1, user with second most uploads would be ranked #2 and so on.

 

Cheers,

Dave

[Q695]

So you want to add a score number column to each table, and have it total all the rows, and average it out for a grade on the teachers submissions to the network?

 

I would look up the total score based on log in id, the average it based upon number of submissions.

Edited April 16, 2013 by Q695

[Barand]

try

$sql = "SELECT ranks.member_id, rank, total, members.member_firstname, members.member_lastname
FROM (
    SELECT member_id, 
    @row := @row+1,
    @rank := IF(total=@lasttotal, @rank, @row) as rank,
    @lasttotal := total as total
    FROM 
        (
        SELECT member_id, COUNT(*) as total
        FROM uploads
        GROUP BY member_id
        ORDER BY COUNT(*) DESC
        ) as tot
        JOIN (SELECT @row:=0, @lastpcent:=0,@rank:=0) AS init
    ) ranks
    JOIN members USING (member_id)
WHERE member.member_id = {$_SESSION['member_id']}";