Rank stats

[chaoswar4u]

Hi. I run a very high competitve game. Training is key but ive also notice that the gym is killing my server load just on the ranks part. (Many that use the F5 and enter method)

 

If I was to remove the ranks code from the gym I would save myself 4 queries and my server load would be perfect but with it running it pathetic so Ive come to ask for the community for alittle help to try and resolve this issue for me and fellow gamers.

 

As per page load the following is called. Each one generating a query.

 

 

$ir['strank']=get_rank($ir['strength'],'strength');
$ir['agirank']=get_rank($ir['agility'],'agility');
$ir['guarank']=get_rank($ir['guard'],'guard');
$ir['labrank']=get_rank($ir['labour'],'labour');
$ir['fitrank']=get_rank($ir['fitness'],'fitness');
$ir['weaponrank']=get_rank($ir['weaponskill'],'weaponskill');

 

 

The get rank pulls from the global_func.php with the following -

 

 

function get_rank($stat, $mykey)
{
global $ir,$userid,$db,$c;
$q=$db->query("SELECT count(*) FROM userstats us LEFT JOIN users u ON us.userid=u.userid WHERE us.$mykey > $stat AND us.userid != $userid AND u.user_level != 0") ;
return $db->fetch_single($q)+1;
}

 

 

Can either the way the rank is pulled be optimised any better or is there another method to drag the rank without mass queries for such a small job. Pulling 1 query per rank is not acceptable to me but I would like to resolve the issue with some better coding than remove it from the gym page.

 

Many thanks in advance for any help that can be provided.

[Lithium]

Correct me if i am wrong... I have been apart for a few month and haven't even looked to a minimal programming these month... but for what i can rememeber the get_rank query is already pulled out on yet another query... the same one that returns every single stat for the user right? as in $ir['whatever'], so from here, it seems to me that you only need a small math to get the rank and can even forget the get_rank() query

[Zeggy]

SELECT count(`id`) FROM userstats us LEFT JOIN users u ON us.userid=u.userid WHERE us.$mykey > $stat AND u.user_level != 0

Just select the count of one column, I set it to `id` but I don't know if you have that column. Just change it if your id column is named differently.

I removed 'us.userid != $userid' because 'us.$mykey > $stat' already implies that you are excluding the current user.

Last thing to consider is whether or not you really need the u.user_level != 0. If you don't need it then you can remove the entire left join.

[fbiss]

Hey chaoswar4u

What I do, is the gym page creates two $_SESSION variables, one to hold the last updated time, and one to hold an array of the users personal ranks.

when you work out , it checks to see if the current time minus 60 seconds is greater then the session time variable. If it is, then it pulls the ranks , stores the new ranks in the session ranks variable , and updates the session time variable with the current time.

[Analog]

Personally I'd just remove the rank display on the gym page. To me it really serves no purpose there.

[chaoswar4u]

fbiss could you supply me with an example. To be honest ive spent all day on getting this to work and getting no where. Im unsure if I over analising this and making it much harder than it actually is.

[fbiss]

Heres my function, for v2

 

function get_gymranks()
{
global $row, $userid, $db;
$ts = $row['strength'] + $row['agility'] + $row['guard'] + $row['IQ'] + $row['labour'];
$q = $db->query("SELECT
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE  strength > '{$row['strength']}' AND (u.user_level > 0) AND (u.userid <> $userid)) as strength_rank,
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE guard > '{$row['guard']}' AND (u.user_level > 0) AND (u.userid <> $userid)) as guard_rank,
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE agility > '{$row['agility']}' AND (u.user_level > 0) AND (u.userid <> $userid)) as agility_rank,
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE IQ > '{$row['IQ']}' AND (u.user_level > 0) AND (u.userid <> $userid)) as IQ_rank,
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE labour > '{$row['labour']}' AND (u.user_level > 0) AND (u.userid <> $userid)) as labour_rank,
	(SELECT COUNT(u.userid) FROM userstats us LEFT JOIN users u ON (u.userid = us.userid) WHERE strength+agility+guard+IQ+labour > $ts ) as total_rank");
$r = $db->fetch_row($q);
$ranks = array('strength' => $r['strength_rank']+1, 'guard' => $r['guard_rank']+1, 'agility' => $r['agility_rank']+1, 'IQ' => $r['IQ_rank']+1, 'labour' => $r['labour_rank']+1, 'total' => $r['total_rank']+1, );
return $ranks;
}

 

now before display of the ranks i have

if(!$_SESSION['granks'] || $_SESSION['granktime'] < time())
{
$_SESSION['granks'] = get_gymranks();
$_SESSION['granktime'] = time()+60;
}
$row['ranks'] = $_SESSION['granks'];

 

now you can use

$row['ranks']['strength']

$row['ranks']['agility']

etc..

depending on your code, you may need to change all the $row to $ir

[chaoswar4u]

Thanks for the reply fbiss.

 

With the code you provided it helped me to resolve the issue. I run mccodes v2. The get rank query has been left as standard and not changed to your example as not required.

 

Ive done the following edits.

 

In gym.php add above stats -

 

 

// Session resolve to reduce query load from mass training
if($_SESSION['granktime'] < time())
{
$_SESSION['strank'] = get_rank($ir['strength'],'strength');
$_SESSION['agirank'] = get_rank($ir['agility'],'agility');
$_SESSION['guarank'] = get_rank($ir['guard'],'guard');
$_SESSION['labrank'] = get_rank($ir['labour'],'labour');
$_SESSION['granktime'] = time()+60;
}
// End 

 

 

Then update the select menu so that that rank comes from the query.

 

 

<option style='color:red;' value='Strength'>Strength (Have {$ir['strength']}, Ranked {$_SESSION['strank']})
<option style='color:blue;' value='Agility'>Agility (Have {$ir['agility']}, Ranked {$_SESSION['agirank']})
<option style='color:green;' value='Guard'>Guard (Have {$ir['guard']}, Ranked {$_SESSION['guarank']})
<option style='color:brown;' value='Labour'>Labour (Have {$ir['labour']}, Ranked {$_SESSION['labrank']})

 

 

Seems to work ok this end. When I click my gym first it has 17 Query and when the sessions are set it drops to 11 Query until 60 seconds session update. This can be set to what ever you like.

 

Thanks fbiss for your method. Helped to construct my resolution. :P