PHP - Ranking Elements In Txt File

I would like to create something like foursquare badges. When ppl upload files, the most downloaded files will have a new badge, how do i create that?

I am trying to figure out how to assign a number rank to a set of results, but I need to reset the ranking when a certain column changes.  For example:

Brand    Rank
a   1
a   2
a   3
b   1
b   2
b   3

I set $rank=0; and then for each item in my loop I add $rank++;  How do I get the rank to reset in the loop when the brand changes?  Thanks for any help.

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

Hey guys and girls,

Okay so am trying to provide a rank for each user within my database but I cant seem to get it working here is my code

Code: [Select]
SELECT

`Table`.`Id`,
`Table`.`Photo`,
`Table`.`Fname`,
`Table`.`Lname`,
`Table`.`Gender`,
`Table`.`Gd`,
`Table`.`Bd`,
`Table`.`Point`,

(SELECT COUNT(*) + 1 FROM
(SELECT U.Id, T.`User Id` FROM `u_account` U
LEFT JOIN `score_system` T ON `T`.`User Id` = `U`.`Id`) x

WHERE Table.Point > (SELECT SUM(Good)-SUM(Bad) FROM `score_system` GROUP BY `User Id`)
) AS RANK

FROM
(SELECT
`C`.`Id`,
`C`.`Photo`,
`C`.`Fname`,
`C`.`Lname`,
`C`.`Gender`,

CASE WHEN COALESCE( SUM(`S`.`Good`), '') = '' THEN '0' ELSE SUM(`S`.`Good`) END AS Gd,
CASE WHEN COALESCE( SUM(`S`.`Bad`), '') = '' THEN '0' ELSE SUM(`S`.`Bad`) END AS Bd,

CASE WHEN COALESCE( SUM(`S`.`Good` - `S`.`Bad`), '') = ''
THEN '0' ELSE SUM(`S`.`Good` - `S`.`Bad`) END AS Point

FROM
`u_account` AS `C`
LEFT JOIN `score_system` AS `S` ON `C`.`Id` = `S`.`Child Id`

AND (SELECT Start FROM event_schedule WHERE CURDATE() >= Start AND End >= CURDATE()) < `S`.`Date`
AND (SELECT End FROM event_schedule WHERE  CURDATE() >= Start AND End >= CURDATE()) > `S`.`Date`

GROUP BY `C`.`Id`

) AS `Table`

ORDER BY Point DESC, Fname ASC

I cant seem to pull the users rank from this and its donig my head in I've tried different things and they all dont work this is perhaps as close as I've got to figuring it out.

This is the part that creates the users rank now its saying the Subquery returns more than 1 row but I just cant seem to think of away to implement this into my query above so that it displays the rank for each user.

Code: [Select]
(SELECT COUNT(*) + 1 FROM
(SELECT U.Id, T.`User Id` FROM `u_account` U
LEFT JOIN `score_system` T ON `T`.`User Id` = `U`.`Id`) x

WHERE Table.Point >= (SELECT SUM(Good - Bad) FROM `score_system` GROUP BY `User Id`)
) AS RANK

I'm also looking for this to display the same rank for both users with the same points so If bill and ben have 200points then they would both have the same rank but if bill was to have 201points then he would be higher up than ben.

So can anyone lend me a hand.

Thanks in advance

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);
}
}

?>