PHP - Help With Ranking System, Selecting A Rank Between 2 Values

Hello everyone...

My following code is supposed to order rank by from Greatest covertaction but for some reason it is ranking the greatest last eg.
900,000 Rank 1
900,001 Rank 2
900,002 Rank 3

Here is the code:

Code: [Select]
<?php
$q = "SELECT * FROM `accountinfo_db` ORDER BY `covertaction`, `anticovertaction` ASC";
$res = mysql_query($q) or die(mysql_error());
$i = 1;
while($player=mysql_fetch_array($res)){
$id = securevar($player['id']);
$time = time();
$user = securevar($player['username']);
$q = "UPDATE `accountinfo_db` SET `covertrank` = '$i', `lastTurnTime` = '$time' WHERE `id` = '$id'";
if(mysql_query($q)){
echo "Covert & Anti Covert Rank set to $user as ".number_format($i)."!<br />";
} else {
// query failed with an error
// put your error reporting/logging code here...
}
$i++;
}
?>

Any help will be appreciated!
Brian

Hello,

I'm in need of assistance trying to get this PHP Script working and I'm hoping you'll be able to find a solution.

SQL Table Format: http://pastebin.com/LUuu2pLp
Code: [Select]
--
-- Table structure for table `hungergames_records`
--

CREATE TABLE IF NOT EXISTS `hungergames_records` (
  `user_id` int(10) unsigned NOT NULL,
  `victories` int(32) unsigned NOT NULL DEFAULT '0',
  `biggest_kill_streak` int(32) unsigned NOT NULL DEFAULT '0',
  `most_chests_opened` int(32) unsigned NOT NULL DEFAULT '0',
  `total_chests_opened` int(32) unsigned NOT NULL DEFAULT '0',
  `lastlogin` int(32) unsigned NOT NULL DEFAULT '0',
  `longest_lifespan` int(32) unsigned NOT NULL DEFAULT '0',
  `total_lifespan` int(32) unsigned NOT NULL DEFAULT '0',
  `total_points` int(32) unsigned NOT NULL DEFAULT '100',
  `most_points` int(32) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `hungergames_users`
--

CREATE TABLE IF NOT EXISTS `hungergames_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `donor` int(32) unsigned NOT NULL DEFAULT '0',
  `user` varchar(40) NOT NULL,
  `donor_cooldown` int(100) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12641 ;

PHP Code: http://pastebin.com/aQANz0BL
Code: [Select]
<?php
 
include("config.php");
 
$getRankQuery = "SELECT hungergames_users.id, hungergames_records.user_id, hungergames_records.victories, hungergames_records.biggest_kill_streak, hungergames_records.most_chests_opened, hungergames_records.total_chests_opened, hungergames_records.lastlogin, hungergames_records.longest_lifespan, hungergames_records.total_lifespan, hungergames_records.total_points, hungergames_records.most_points, hungergames_users.donor, hungergames_users.user FROM hungergames_records, hungergames_users WHERE hungergames_records.user_id = hungergames_users.id ORDER BY hungergames_records.total_points DESC";
$getRankResult =  mysql_query($getRankQuery);
 
//Source From: http://stackoverflow.com/questions/7704311/calculate-rank-with-points 
function getUserRank($searchedUserID)
{
    $userArray = getAllUsersOrderedByPoints();
    $rank       = 0;
    $lastPoints = -1; // Never happens
    foreach ( $userArray as $user) {
        if ($user['total_points'] != $lastPoints)  $rank++;
        if ($user['user_id'] == $searchedUserID) break;
    }
    return $rank;
}
 
while ($data = mysql_fetch_array($result)) {
echo getUserRank($data["user_id"]);
}
 
?>

What am I trying to accomplish? Simple Ranking System...

Basically, The total points is stored into total_points field for each user and whoever has the highest score will be Ranked #1 and count down to a the full database (Currently at 12,641 users)

Now the above function getUserRank is the code I pulled from the above source listed, I think my issue relies in the function of getAllUsersOrderedByPoints(); - I've tried doing something like.

Code: [Select]
$row = mysql_fetch_array($getRankResult);

Then adding:

    $userArray = $row["user_id"];

Didn't work - So if you can help me correct this than I'll be greatly appertiated.

Lastly, I've tried doing:

Code: [Select]
$index = 1;
  while ($data = mysql_fetch_array($getRankQuery)) {
    echo $index++ . " " . $data["id"] . " ".$data["user"]." <br>\n";
}

This solution works, but this solution would not work for when a "User is Search" it would count them as the highest rank 1,2,3 and wouldn't work, so either the function up there needs to be corrected or please help get a working solution, I've been googleing this issue and seems too be a common code issue with a lot people are having and not many solutions.

The application that records data (Not PHP) doesn't store any data with a "Rank" table otherwise this would be a simple echo'ed out data.

For a better understanding of what I'm trying to do - You may take a look at: http://justonemoreblock.com/lb/leaderboards.php

Regards,
Cory

Hello everyone... I made point system that ranks users in my app.  There are two parts to the system, a primary score and a dynamic score.  The primary score is based on how you well you perform and the dynamic score is based on how well your friends perform.  Both of these get added together to equal your total score.  The problem is that I have a lot of looping occurring which results in about 400 queries being run when I access the page that executes this class.  Can this be optimized?  What's the best way to do this?  I don't want to store the data as I want this presented in real-time.

Code: [Select]
<?php

class edge
{
public function get_all_users_edge()
{
$sql = "SELECT id FROM users WHERE status = 3";

$result = mysql_query($sql) or die(mysql_error());

$ids = array();
while (($row = mysql_fetch_assoc($result)) !== false)
{
$ids[] = $row;
}

$all_edges = array();

foreach ($ids as $k => $id)
{
$edge = $this->get_users_edge($id['id']);
$all_edges[$k] = $edge;

}

return $all_edges;
}

public function get_users_edge($uid)
{
$users_primary_edge = $this->get_users_primary_edge($uid);
$users_dynamic_edge = $this->get_users_dynamic_edge($uid);

return $users_primary_edge + $users_dynamic_edge;
}

public function get_users_primary_edge($uid)
{
if (active_id($uid) === false)
{
return false;
}

$uid = (int)$uid;

$sql = "SELECT
CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`,
CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`,
`companies`.`companyvideo` AS `video`,
`investor_info`.`accredited` AS `accredited`,
           ( SELECT COUNT(`user_id`) FROM `blog_posts` WHERE `user_id` = '${uid}' ) AS `post_count`,
           ( SELECT COUNT(`uid`) FROM `votes` WHERE `uid` = '${uid}' ) AS `vote_sent_count`,
`votes_received`.`inv_vote_received_count`,
`votes_received`.`pub_vote_received_count`,
           ( SELECT COUNT(`userid`) FROM `employees` WHERE `userid` = '${uid}' ) AS `joined_count`,
`votes2`.`ent_count`,
`votes2`.`inv_count`,
(
SELECT
COUNT(`company_id`)
FROM `company_fans`
LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid`
LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid`
WHERE `userid` = '${uid}'
) AS `company_fans_count`,
        ( 
         SELECT 
         COUNT(`user_id`) 
         FROM `recommendations` 
         WHERE `user_id` = '${uid}' 
        ) AS `recommendation_count`,
        ( 
         SELECT 
         COUNT(`partner_id`) 
         FROM `partners` 
         WHERE (`user_id` = '${uid}' OR `friend_id` = '${uid}') 
         AND `approved` = '1' 
        ) AS `associate_count`
FROM `users`
        LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id`
LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id`
LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid`
LEFT JOIN (
SELECT
`employees`.`userid`,
               SUM( IF( `votes`.`vote_type` = 1, 1, 0 ) ) AS `inv_vote_received_count`,
               SUM( IF( `votes`.`vote_type` = 0, 1, 0 ) ) AS `pub_vote_received_count`
FROM `votes`
INNER JOIN `employees` ON `employees`.`companyid` = `votes`.`company_id`
WHERE `employees`.`userid` = '${uid}'
             GROUP BY `employees`.`userid`
         ) AS `votes_received` ON `votes_received`.`userid` = `users`.`id`
LEFT JOIN (
SELECT
`beta_keys`.`ref_id`,
               SUM( IF( `users`.`accounttype` = 1, 1, 0 ) ) AS `ent_count`,
               SUM( IF( `users`.`accounttype` = 0, 1, 0 ) ) AS `inv_count`
FROM `beta_keys`
INNER JOIN `users` ON `users`.`id` = `beta_keys`.`userid`
WHERE `beta_keys`.`ref_id` = '${uid}' AND `users`.`status` = 3
             GROUP BY `users`.`id`
         ) AS `votes2` ON `votes2`.`ref_id` = `users`.`id`
WHERE `users`.`id` = '${uid}'";

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$score = 0;

if((!empty($row['cred_chars'])) && (!empty($row['spec_chars'])))
{
$score += 200;
}

if(!empty($row['video']))
{
$score += 50;
}

if($row['accredited'] == 1)
{
$score += 100;
}

$score += (50 * $row['post_count']);
$score += (5 * $row['vote_sent_count']);
$score += (3 * $row['inv_vote_received_count']);
$score += (1 * $row['pub_vote_received_count']);
$score += (200 * $row['joined_count']);
$score += (40 * $row['ent_count']);
$score += (200 * $row['inv_count']);
$score += (10 * $row['company_fans_count']);
$score += (30 * $row['recommendation_count']);
$score += (20 * $row['associate_count']);

return $score;
}

public function get_users_dynamic_edge($uid)
{
$uid = (int)$uid;

$sql = "(
SELECT 
`users`.`id`
FROM partners
INNER JOIN `users`
ON `partners`.`user_id` = `users`.`id`
WHERE partners.friend_id = '${uid}'
AND `approved` = 1
)
UNION ALL
(
SELECT 
`users`.`id`
FROM `partners`
INNER JOIN `users`
ON `partners`.`friend_id` = `users`.`id`
WHERE `partners`.`user_id` = '${uid}'
AND `approved` = 1
)";

$result = mysql_query($sql) or die(mysql_error());

$i  = 0;
$score  = 0;

while (($row = mysql_fetch_assoc($result)) !== false)
{
$dynamic_scores[$i] = array(
'uid'  => $row['id'],
'score' => $this->get_users_primary_edge($row['id']),
);

$i++;
}

if(!empty($dynamic_scores))
{
foreach($dynamic_scores as $k => $dynamic_score)
{
$score += $dynamic_score['score'];
}
}

return ($score * 0.1);
}
}

?>

Hi

I've set-up a multidimensional PHP array from a SQL query which pulls back parcels despatched per item on a daily basis.

e.g.

Item|Day1|Day2 |Day3|Day4|Day5
1   |100 | 120 | 90 |150 |60
2   |150 | 200 | 80 |90  |100
3   | 1  |  2  | 3  | 4  | 5

I want to be able to assign a ranked value to each day based upon the amount of items sent out each day and put that either into the existing array, or create a new array to which it can reference

I want to query the five heighest values in a coloum but the coloum is not the primarykey.

i.e: i have a coloum on the end of my table (articles) called views which get updated when ever an article is viewed by adding 1. On anther page i want to be able to view the 5 most viewed articles.