Help Optimize My Ranking System Class?

[unemployment]

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.

 

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

?>