AAC Gesior topfraggers by storage

[Jakubens]

Hello, i got this script:

<?php
if(!defined('INITIALIZED'))
    exit;
$main_content .= '<div style="text-align: center; font-weight: bold;">Top 30 fraggers on ' . htmlspecialchars($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;">Top</td>
        <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 COUNT(1) frags, `players`.`name` FROM `players`, `player_deaths` WHERE `is_player` = 1 AND `player_deaths`.`player_id` = `players`.`id` GROUP BY `killed_by` ORDER BY ' . $SQL->fieldName('frags') . ' DESC, ' . $SQL->fieldName('name') . ' ASC LIMIT 30;') as $player)
{
    $i++;
    $main_content .= '<tr bgcolor="' . (is_int($i / 2) ? $config['site']['lightborder'] : $config['site']['darkborder']) . '">
        <td style="text-align: center"> '.$i.'.</td>
        <td><a href="?subtopic=characters&name=' . urlencode($player['name']) . '">' . htmlspecialchars($player['name']) . '</a></td>
        <td style="text-align: center;">' .$player['frags'].'</td>
    </tr>';
}
$main_content .= '</table>';

and i would like to change this query

foreach($SQL->query('SELECT COUNT(1) frags, `players`.`name` FROM `players`, `player_deaths` WHERE `is_player` = 1 AND `player_deaths`.`player_id` = `players`.`id` GROUP BY `killed_by` ORDER BY ' . $SQL->fieldName('frags') . ' DESC, ' . $SQL->fieldName('name') . ' ASC LIMIT 30;') as $player)

to make toplist from storage XXX, but I don't know how to edit SQL queries.

@up

@up

@up

@up xD

 

[imkingran]

Change this line below to your key:
$storageKey = 123456;

<?php
if(!defined('INITIALIZED'))
    exit;
$main_content .= '<div style="text-align: center; font-weight: bold;">Top 30 fraggers on ' . htmlspecialchars($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;">Top</td>
       <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;
$storageKey = 123456; // Change this to your storage Key

foreach($SQL->query("SELECT `value`, `player_id` FROM `player_storage` WHERE `key` = ".$storageKey." AND `value` > 0 ORDER BY `value` DESC LIMIT 100") as $player)
{
    $i++;
    $pName = $SQL->query('SELECT '.$SQL->fieldName('name').' FROM '.$SQL->tableName('players').' WHERE '.$SQL->fieldName('id').' = '.$player['player_id'].'')->fetch();
    $main_content .= '<tr bgcolor="' . (is_int($i / 2) ? $config['site']['lightborder'] : $config['site']['darkborder']) . '">
       <td style="text-align: center"> '.$i.'.</td>
       <td><a href="?subtopic=characters&name=' . urlencode($pName['name']) . '">' . htmlspecialchars($pName['name']) . '</a></td>
       <td style="text-align: center;">' .$player['value'].'</td>
   </tr>';
}
$main_content .= '</table>';

 

[Danger II]

$SQL->query("SELECT `value`, `player_id` FROM `player_storage` WHERE `key` = ".$storageKey." AND `value` > 0 ORDER BY `value` DESC LIMIT 100") as $player)

Maybe change the limit to 30 instead, cause we've got here Top 30 of Fraggers

$SQL->query("SELECT `value`, `player_id` FROM `player_storage` WHERE `key` = ".$storageKey." AND `value` > 0 ORDER BY `value` DESC LIMIT 30") as $player)

Overall, good job.


Edit:

I've noticed that in player_storage the value column is a varchar in older distributions such as TFS 0.4, probably older engines aswell.
forgottenserver036pl1/mysql.sql at master · peonso/forgottenserver036pl1 · GitHub
global860/schema.sql at master · otservme/global860 · GitHub

You should use this line instead then, else the list wont be in right order:

foreach($SQL->query("SELECT `value`, `player_id` FROM `player_storage` WHERE `key` = ".$storageKey." AND `value` > 0 ORDER BY convert(`value`, decimal) DESC LIMIT 30") as $player)