[RELEASE]More Server Statistics

[stevnnnn]

Hey, I haven't seen this anywhere yet, and it's rather simple. What it does is it shows the total of mesos, votepoints, nx, str and others in the server onto your site. It only gets the data from players.

Add this to where ever you want on the site.

<?php
    $vpcolumn = "votepoints"
    $nxcolumn = "paypalNX"
    $acquery = mysql_query("SELECT sum($vpcolumn),sum($nxcolumn) FROM accounts where gm = '0'");
    $chquery = mysql_query("SELECT sum(reborns), sum(meso), sum(fame), sum(str), sum(`int`), sum(dex), sum(luk), sum(ap) FROM characters where gm = '0'");
    $maxstatquery = mysql_query("SELECT * FROM characters where gm = '0' AND str = '32767' AND dex = '32767' AND luk = '32767' AND `int` = '32767'");
    $acrow = mysql_fetch_array($acquery);
    $chrow = mysql_fetch_array($chquery);
    $maxstatsrow = mysql_num_rows($maxstatquery);
 echo '<div id="mstatistics">';
   echo number_format($acrow['sum('.$vpcolumn.')']).' vote points in total.';
   echo number_format($acrow['sum('.$nxcolumn.')']).' NX in total.';
   echo number_format($chrow['sum(reborns)']).' rebirths done.';
   echo number_format($chrow['sum(meso)']).' mesos in total.';
   echo number_format($chrow['sum(fame)']).' fame in total.';
   echo number_format($chrow['sum(str)']).' STR in total.';
   echo number_format($chrow['sum(dex)']).' DEX in total.';
   echo number_format($chrow['sum(`int`)']).' INT in total.';
   echo number_format($chrow['sum(luk)']).' LUK in total.';
   echo number_format($chrow['sum(ap)']).' AP in total available for use.';
   echo number_format($maxstatsrow).' characters with all max stats.';  
echo '</div>';
    ?>

You could add this into a horizontal scrolling bar, pherhaps even shuffle the order.

Replace it with this to shuffle it.

Spoiler

<?php
    $vpcolumn = "votepoints";
    $nxcolumn = "paypalNX";
    $acquery = mysql_query("SELECT sum($vpcolumn),sum($nxcolumn) FROM accounts where gm = '0'");
    $chquery = mysql_query("SELECT sum(reborns), sum(meso), sum(fame), sum(str), sum(`int`), sum(dex), sum(luk), sum(ap) FROM characters where gm = '0'");
    $maxstatquery = mysql_query("SELECT * FROM characters where gm = '0' AND str = '32767' AND dex = '32767' AND luk = '32767' AND `int` = '32767'");
    $acrow = mysql_fetch_array($acquery);
    $chrow = mysql_fetch_array($chquery);
    $maxstatsrow = mysql_num_rows($maxstatquery);
    $shufflethis = array(number_format($acrow['sum('.$vpcolumn.')']).' vote points in total.', number_format($acrow['sum('.$nxcolumn.')']).' NX in total.', number_format($chrow['sum(reborns)']).' rebirths done.', number_format($chrow['sum(meso)']).' mesos in total.', number_format($chrow['sum(fame)']).' fame in total.', number_format($chrow['sum(str)']).' STR in total.', number_format($chrow['sum(dex)']).' DEX in total.', number_format($chrow['sum(`int`)']).' INT in total.', number_format($chrow['sum(luk)']).' LUK in total.', number_format($chrow['sum(ap)']).' AP in total available for use.', number_format($maxstatsrow).' characters with all max stats.');
    shuffle($shufflethis);
    foreach($shufflethis as $echos) {
    echo $echos;
    echo '</br>';
    }
    ?>

Or even make it only display on one line, and vertically scroll between each data.
You can do this using jQuery.

Add this just before </head> add

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

Then add this to where you want to display the line.

Spoiler

     <script>
     $(document).ready(function() {
     scrollmstats();
    function scrollmstats() {
    $('#mstats_display').slideUp(function() {$('#mstats_display').html($('#mstats li:first').html());$('#mstats li:first').appendTo('#mstats');$('#mstats_display').slideDown();});
    }
    setInterval(function() {scrollmstats()}, 4000);//How many miliseconds for it to scroll
    });
     </script>
     <?php
    $vpcolumn = "votepoints";
    $nxcolumn = "paypalNX";
    $acquery = mysql_query("SELECT sum($vpcolumn),sum($nxcolumn) FROM accounts where gm = '0'");
    $chquery = mysql_query("SELECT sum(reborns), sum(meso), sum(fame), sum(str), sum(`int`), sum(dex), sum(luk), sum(ap) FROM characters where gm = '0'");
    $maxstatquery = mysql_query("SELECT * FROM characters where gm = '0' AND str = '32767' AND dex = '32767' AND luk = '32767' AND `int` = '32767'");
    $acrow = mysql_fetch_array($acquery);
    $chrow = mysql_fetch_array($chquery);
    $maxstatsrow = mysql_num_rows($maxstatquery);
    $shufflethis = array(number_format($acrow['sum('.$vpcolumn.')']).' vote points in total.', number_format($acrow['sum('.$nxcolumn.')']).' NX in total.', number_format($chrow['sum(reborns)']).' rebirths done.', number_format($chrow['sum(meso)']).' mesos in total.', number_format($chrow['sum(fame)']).' fame in total.', number_format($chrow['sum(str)']).' STR in total.', number_format($chrow['sum(dex)']).' DEX in total.', number_format($chrow['sum(`int`)']).' INT in total.', number_format($chrow['sum(luk)']).' LUK in total.', number_format($chrow['sum(ap)']).' AP in total available for use.', number_format($maxstatsrow).' characters with all max stats.');
    shuffle($shufflethis);
    echo '<span id="mstats_display"></span><ul style="display:none;" id="mstats">';
    foreach($shufflethis as $echos) {
    echo '<li>';
    echo $echos;
    echo '</li>';
    }
    echo '</ul></div>';
    ?>

If you want for it to fade in or out instead, replace the contents of <script></script> from the above with this:

Spoiler

$(document).ready(function() {
     scrollmstats();
    function scrollmstats() {
    $('#mstats_display').fadeTo(800, 0, function() {$('#mstats_display').html($('#mstats li:first').html());$('#mstats li:first').appendTo('#mstats');$('#mstats_display').fadeTo(800 ,1);});
    }
    setInterval(function() {scrollmstats()}, 4000);//How many miliseconds for it to scroll
    });

If you get any error, then the column name is probably wrong. Change $vpcolumn or $nxcolumn to match the one in your database.

 

[holthelper]

first off there is no point in adding extra stats that are pointless and if your going to use jquery to display this info output the results in json

 

[Diglett]

This will basically make your site load slower, due to it fetching your MySQL db all the time.
But anyways, guess this is useful for someone.

 

[Sparks]

^ On top of what @Diglett said. Not only will this make your site load slower, but the constant querying for results can and will lock your sql. This is just too... exploitable.. You can crash a server with enough re-querying. And the worst part about it, is that it's so unnecessary to access these tables (outside of the game itself), that there's no real way to "secure" it.

You cannot limit innodb / mysqld fully from an incoming connection/query results for this type of thing, without limiting their in-game access as well, even indexing, so if you do put connectivity rules with these, not only will you have a slow site load, but a lot of angry players not being able to access the game without being blocked from MySQL on the back end. which will just cause both sql + source exceptions left and right.

Generally speaking this is from my experience.

 

[holthelper]

IMO best results would be to cache the results to say a file and refresh the results hourly for "most" up-to-date stats