• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

[0.3.6-0.4] Top fragers and top guilds with one database query

elf

Sing, sing blue silver
Senator
Joined
Dec 11, 2007
Messages
3,666
Solutions
1
Reaction score
125
Location
Warsaw, Poland
GitHub
tayandenga
Twitch
tayandenga
Its obvious when I see guys posting such shit, or selling worse one like AndersPro does.
Here are light, not requiring any extra events nor database fields Gesior AAC modules.

frags.php
PHP:
<?php
$main_content .= '<div style="text-align: center; font-weight: bold;">Top 30 frags on ' . $config['server']['serverName'] . '</div>
<table border="0" cellspacing="1" cellpadding="4" width="100%">
    <tr bgcolor="' . $config['site']['vdarkborder'] . '">
        <td class="white" style="text-align: center; font-weight: bold;">Name</td>
        <td class="white" style="text-align: center; font-weight: bold;">Frags</td>
    </tr>';

$i = 0;
foreach($SQL->query('SELECT `p`.`name` AS `name`, COUNT(`p`.`name`) as `frags`
    FROM `killers` k
    LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id`
    LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id`
WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1
    GROUP BY `name`
    ORDER BY `frags` DESC, `name` ASC
    LIMIT 0,30;') as $player)
{
    $i++;
    $main_content .= '<tr bgcolor="' . (is_int($i / 2) ? $config['site']['lightborder'] : $config['site']['darkborder']) . '">
        <td><a href="?subtopic=characters&name=' . urlencode($player['name']) . '">' . $player['name'] . '</a></td>
        <td style="text-align: center;">' . $player['frags'] . '</td>
    </tr>';
}

$main_content .= '</table>';
?>

and top guilds, which you should place in latestnews.php- anywhere you wish:
Code:
$main_content .= '<div class="NewsHeadline">
    <div class="NewsHeadlineBackground" style="background-image:url(' . $layout_name . '/images/news/newsheadline_background.gif)">
        <table border="0">
            <tr>
                <td style="text-align: center; font-weight: bold;">
                    <font color="white">Most powerfull guilds</font>
                </td>
            </tr>
        </table>
    </div>
</div>
<table border="0" cellspacing="3" cellpadding="4" width="100%">
    <tr>';

foreach($SQL->query('SELECT `g`.`id` AS `id`, `g`.`name` AS `name`,
    `g`.`logo_gfx_name` AS `logo`, COUNT(`g`.`name`) as `frags`
FROM `killers` k
    LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id`
    LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id`
    LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id`
    LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1
    GROUP BY `name`
    ORDER BY `frags` DESC, `name` ASC
    LIMIT 0, 4;') as $guild)
    $main_content .= '        <td style="width: 25%; text-align: center;">
            <a href="?subtopic=guilds&action=show&guild=' . $guild['id'] . '"><img src="guilds/' . ((!empty($guild['logo']) && file_exists('guilds/' . $guild['logo'])) ? $guild['logo'] : 'default_logo.gif') . '" width="64" height="64" border="0"/><br />' . $guild['name'] . '</a><br />' . $guild['frags'] . ' kills
        </td>';

$main_content .= '    </tr>
</table>';


Demo of guilds: http://wypas.eu/.
Have fun.

For Znote AAC:
No prob, elf got some awesome queries there. :)

(each script is an own page, just modify it if you want to have it on your news section etc).

Top fraggers:
PHP:
<?php require_once 'engine/init.php'; include 'layout/overall/header.php';

    // Cache the results
    $cache = new Cache('engine/cache/topMurders');
    if ($cache->hasExpired()) {
        $killers = mysql_select_multi("SELECT `p`.`name` AS `name`, COUNT(`p`.`name`) as `frags` FROM `killers` k LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id` LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id` WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0,30;");
       
        $cache->setContent($killers);
        $cache->save();
    } else {
        $killers = $cache->load();
    }

if (!empty($killers) || $killers !== false) {
?>

<table id="onlinelistTable" class="table table-striped table-hover">
    <tr class="yellow">
        <th>Name:</th>
        <th>Frags:</th>
    </tr>
    <?php foreach ($killers as $killer) { ?>
    <tr>
        <td><?php echo $killer['name']; ?></td>
        <td><?php echo $killer['frags']; ?></td>
    </tr>
    <?php } ?>
</table>

<?php
} else echo '<h1>No frags yet.</h1>';
 include 'layout/overall/footer.php'; ?>

Top guilds:
PHP:
<?php require_once 'engine/init.php'; include 'layout/overall/header.php';

    // Cache the results
    $cache = new Cache('engine/cache/topGuilds');
    if ($cache->hasExpired()) {
        $guilds = mysql_select_multi("SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `killers` k LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id` LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id` LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id`    LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id` WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 4;");
        
        $cache->setContent($guilds);
        $cache->save();
    } else {
        $guilds = $cache->load();
    }

if (!empty($guilds) || $guilds !== false) {
?>

<table id="onlinelistTable" class="table table-striped table-hover">
    <tr class="yellow">
        <th>Name:</th>
        <th>Frags:</th>
    </tr>
    <?php foreach ($guilds as $guild) {

    $url = url("guilds.php?name=". $guild['name']);
    echo '<tr class="special" onclick="javascript:window.location.href=\'' . $url . '\'">'; ?>
        <td><?php echo $guild['name']; ?></td>
        <td><?php echo $guild['frags']; ?></td>
    </tr>
    <?php } ?>
</table>

<?php
} else echo '<h1>No frags yet.</h1>';
 include 'layout/overall/footer.php'; ?>
 
Last edited by a moderator:
Back
Top