[Request] Searching for a PRO SQL Scripter! Avoid pointless replays

[Kavvson]

Iam looking for a script that would sum some values from the database (guild tables).

The script would output guild points.

I tried to make it like the top fraggers script but i was to hard for me. ;(

Here are the basic informations.

As you know top fraggers displayed TOP 3 (limit can be extended) guild fraggers basically it added all frags made by the guild.

Mine script would be based on ::

a)Sum all levels in guild
b)Sum all members(Count members)
c)Sum top skills from the guild
d)Top Level of the guild members
e)Lowest level of the guild members
f)AVG Level of the guild members

The script was made by Mipo91

$allM = $connection->query('SELECT SUM(`level`) as `level1`, AVG(`level`) as `level2`, (SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') ORDER BY `level` ASC LIMIT 1) AS `name1`,(SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') ORDER BY `level` DESC LIMIT 1) AS `name2`,(SELECT COUNT(*) FROM `guild_invites` WHERE `guild_id` = ' . $guild_id .') AS `invite`, (SELECT COUNT(1) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') AND online = 1) AS `online`, (SELECT COUNT(`gr`.`id`) FROM `players` AS `p` LEFT JOIN `guild_ranks` AS `gr` ON `gr`.`id` = `p`.`rank_id` WHERE `gr`.`guild_id` = ' . $guild_id. ') as `total` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = ' . $guild_id . ') ')->fetch();

I made a script http://otland.net/f118/modern-aac-injection-guild-statistics-v3-106828/

So here it goes the script will a)+b)+c)+d)+e)+f)

Like the top fraggers it will display Top 3 guilds (LIMIT 3 f.e)

What i want do to with it?

Make a Modern AAC script (new page) like top fraggers.

For sure credits etc. I have the graphic conception and some extras.

Im sure if you are a good scripter you can deal with this. What did blocked me?
-Generally the $guild_id how would i get it if its a foreach script with LIMIT 3
-How to display the top skills? :| Mine script (sql by Mipo91) shows the top skills also from a foreach so i dont know (

foreach ($skills as $key => $value) {
    $allM5 = $connection->query('
        SELECT`p`.`name`AS`Name`, `ps`.`value`AS`Sword`, `p`.`maglevel`, `p`.`experience`FROM`players`AS`p`JOIN`player_skills`AS`ps`WHERE`ps`.`player_id` = `p`.`id`AND`ps`.`skillid` = ' . $key . '
        AND`rank_id`IN(SELECT`id`FROM`guild_ranks`WHERE`guild_id` = ' . $guild_id . ') ORDER BY`Sword`DESC Limit 1 ')->fetch();
    echo "<tr align=center><td><img style=\"border:medium none;\" src=\"" . WEBSITE . "/" . INJECTION_PATH . "/images/skills/" . $key . ".png\"></td><td><div class=i_name>" . $allM5[0] . "</div></td><td><div class=i_skill>" . $allM5[1] . " : " . $value . "</div></td></tr>";
}

Regards

 

[MiPo91]

Here it is without (C).

echo ' 
<table border="0" cellspacing="3" cellpadding="4" width="100%" style="text-align: center;">
<tr>
	<td>Guild</td>
	<td>Guild Points</td>
</tr> ';

foreach($SQL->query('
		SELECT 
			`g`.`id` AS `id`,
			`g`.`name` AS `name`,
			SUM(`p`.`level`) AS `level`,
			COUNT(`p`.`name`) AS `count`,
			AVG(`p`.`level`) AS `average`,
			MIN(`p`.`level`) AS `min`,
			MAX(`p`.`level`) AS `max`
		FROM `players` p
			LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id`
			LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
		WHERE `g`.`id` = `g`.`id`
		GROUP BY `name`
		ORDER BY `level` DESC
		LIMIT 5
	') as $guild) 
	{
		$Points = $guild['level'] + $guild['count'] + round($guild['average']) + $guild['min'] + $guild['max'];
		echo '	<tr><td style="width: 25%;">
				<a href="'.WEBSITE.'/index.php/guilds/view/' . $guild['id'] . '"><img src="'.WEBSITE.'/public/guild_logos/' . ((file_exists('public/guild_logos/' . $guild['id'] . '.gif')) ? $guild['id'].'.gif' : 'default.gif') . '" width="64" height="64" border="0"/><br />' . $guild['name'] . '</a>
				</td><td>'.$Points.'
				</td></tr>';
	}
echo '</table>';