Echoing out a field and a value from one row while running a query that ranks all rows

Question

Hello,

I have a MySQL query called $sqlStr5 that ranks rows by a metric called totalScore2. One of the fields that $sqlStr5 returns is called username.

I would like to echo out the rank and the value of totalScore2 where username equals a variable called $u.

How can I do this?

Below is what I have so far.

Thanks in advance,

John

$result = mysql_query($sqlStr5);
$count = 1;  
$arr = array();

    while ($row = mysql_fetch_array($result)) { 

            echo '<div class="sitename1edit2a">'.$count++.'.</div>';
            echo '<div class="sitename1edit2">'.number_format(($row["totalScore2"])).'</div>';


        }

Answer 1

This should work:

$result = mysql_query($sqlStr5);
$count = 1;

while($row = mysql_fetch_array($result))
{
    if($u == $row['username'])
    {
        echo '<div class="sitename1edit2a">'.$count.'</div>';
        echo '<div class="sitename1edit2">'.number_format($row["totalScore2"]).'</div>';
    }
    $count++;
}

Note however that this is not the most efficient way. It should be possible to make a SQL query that returns the rank and the total score for user $u.

Answer 2

The following solution uses a stored procedure to work out a user's rank based on a simple ranking system which you could replace with your own more complex one.

call get_user_rank(<user_id>) 

I've added another stored procedure which lists the top ten ranks and flags whether the user_id passed in is included in the top ten as I remember that being a requirement from another question of yours.

call list_top_ten_ranks(<user_id>)

Testing (call these sprocs from your php)

select count(*) from users;
count(*)
========
250000

call get_user_rank(54193);
-- 0:00:00.300: Query OK

call get_user_rank(1);
-- 0:00:00.291: Query OK

call list_top_ten_ranks(54193);
-- 0:00:00.208: Query OK

call list_top_ten_ranks(1);
-- 0:00:00.215: Query OK

PHP

$result = $conn->query(sprintf("call get_user_rank(%d)", 1));
$row = $result->fetch_assoc();
$result->close();

echo sprintf("user = %s rank = %s points = %s<br/>", 
    $row["user_id"],$row["rank"],$row["points"]);

$conn->close();

Hope some of this proves useful.

Script

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
points smallint unsigned not null default 0
)
engine=innodb;

-- PROCEDURES

drop procedure if exists get_user_rank;
delimiter #

create procedure get_user_rank
(
p_user_id int unsigned
)
proc_main:begin

-- replace this simple ranking method by your own

set @rank = 0;

create temporary table tmp engine=memory
select
 @rank:=@rank+1 as rank,
 u.user_id,
 u.points
from
 users u
order by 
 u.points desc, u.user_id;

select * from tmp where user_id = p_user_id;

drop temporary table if exists tmp;

end proc_main #

delimiter ;

drop procedure if exists list_top_ten_ranks;
delimiter #

create procedure list_top_ten_ranks
(
p_user_id int unsigned
)
proc_main:begin

-- replace this simple ranking method by your own

set @rank = 0;
set @in_top_ten = 0;

create temporary table tmp engine=memory
select
 @rank:=@rank+1 as rank,
 u.user_id,
 u.points
from
 users u
order by 
 u.points desc, u.user_id
limit 10;

if exists (select 1 from tmp where user_id = p_user_id) then
  set @in_top_ten = 1;
end if;

select tmp.*, @in_top_ten as in_top_ten from tmp order by tmp.rank;

drop temporary table if exists tmp;

end proc_main #

delimiter ;