[PHP] Google Graphs Server

DAS · January 1, 2011, 3:57pm

if you don’t have the basic php/html knowledge don’t try it. I’ve deleted server 2 stats. You must delete server 2 from the output…

some tips:

notice the #!/usr/bin/php

at the beginning of the file ?.

you can uncomment the // before the echos to debug this script

[CODE]#!/usr/bin/php

<?php $Address = ''; // Stats script root folder > Example: www.mydomain.name/stats $Hostname = ''; // MySQL DB Server IP/Domain $Username = ''; // MySQL Username $Password = ''; // MySQL Password $CharacterDatabase = ''; // must contains characters table $RealmDatabase = ''; // must contains zone_coordinates and accounts table $realm_db = mysql_connect($Hostname, $Username, $Password); mysql_select_db($RealmDatabase, $realm_db) or die("Eroare #1"); //--------- server uptime ------ //echo "
-----------------SERVER UPTIME ----------
"; $uptime = "SELECT uptime FROM uptime WHERE realmid=1 ORDER BY starttime DESC LIMIT 1"; if(!$res = mysql_query($uptime)) die(mysql_error()); $row = mysql_fetch_assoc($res); $uptime_server1 = $row['uptime']; //add more ? //echo "Uptime server FUN: ".date("H:i",$uptime_server1)." hours"; $uptime_server1 = date("H:i",$uptime_server1)." hours"; //-- total accounts-------- //echo "
-----------------ACCOUNTS ----------
"; $query="SELECT max(id) from account"; $res = mysql_query($query); $total_accounts = mysql_fetch_assoc($res); $total_accounts = $total_accounts['max(id)']; //echo "Total accounts: ".$total_accounts; $acu24h = date( 'Y-m-d H:i:s', time()-60*60*24 ); $query="SELECT COUNT(*) FROM account WHERE joindate>'$acu24h'"; $res = mysql_query($query); $new_accounts = mysql_fetch_assoc($res); $new_accounts = $new_accounts['COUNT(*)']; //echo "
New accounts last 24h: ".$new_accounts; //-- char statistics-------- //echo "
-----------------CHARACTERS ----------
"; $query="SELECT avg(numchars) FROM realmcharacters"; $res = mysql_query($query); $avg_chars = mysql_fetch_assoc($res); $avg_chars = $avg_chars["avg(numchars)"]; //echo "One account has an average of ".round($avg_chars,2)." chars"; $avg_chars = round($avg_chars,2); $query="SELECT sum(numchars) FROM realmcharacters WHERE realmid=1"; $res = mysql_query($query); $total_chars1 = mysql_fetch_assoc($res); $total_chars1 = $total_chars1["sum(numchars)"]; //echo "
FUN server has a total of ".$total_chars1." chars"; $query="SELECT sum(numchars) FROM realmcharacters WHERE realmid=2"; $res = mysql_query($query); $total_chars2 = mysql_fetch_assoc($res); $total_chars2 = $total_chars2["sum(numchars)"]; //echo "
HIGH server has a total of ".$total_chars2." chars"; //----------------------------------------- char specifics -------------- mysql_select_db($CharacterDatabase, $realm_db) or die("Eroare #4"); //---- cool specifics -------- //echo "
-----------------Cool stats (Server Fun) ----------
"; $query="SELECT sum(totalKills) FROM characters"; $res = mysql_query($query); $total_kills = mysql_fetch_assoc($res); $total_kills = $total_kills["sum(totalKills)"]; //echo $total_kills. " kills in our servers"; $query="SELECT sum(yesterdayKills) FROM characters"; $res = mysql_query($query); $yesterday_kills = mysql_fetch_assoc($res); $yesterday_kills = $yesterday_kills["sum(yesterdayKills)"]; //echo "
".$yesterday_kills. " yesterday kills"; $player_query = mysql_query("SELECT (SELECT COUNT(guid) FROM $CharacterDatabase.`characters` WHERE race IN(2,5,6,8,10)) as horde, (SELECT COUNT(guid) FROM $CharacterDatabase.`characters` WHERE race IN(1,3,4,7,11)) as alliance FROM $CharacterDatabase.`characters`")or die(mysql_error()); $player_results = mysql_fetch_array($player_query); $horde = $player_results['horde']; $alliance = $player_results['alliance']; //echo "
-----------------Horde | Alliance ----------
"; //echo "
Horde: ".$horde; //echo "
Alliance: ".$alliance; //echo "
-----------------Gender Distribution ----------
"; $query="SELECT COUNT(*) FROM characters WHERE gender=1"; $res = mysql_query($query); $race1 = mysql_fetch_assoc($res); $female = $race1['COUNT(*)']; $male = $horde+$alliance-$female; //echo "
Male: ".$male; //echo "
Female: ".$female; $male_perc = ($male*100)/($male+$female); $female_perc = ($female*100)/($male+$female); //echo "
-----------------Chars Distribution by Race (Server Fun) ----------
"; $query="SELECT COUNT(*) FROM characters WHERE race=1"; $res = mysql_query($query); $race1 = mysql_fetch_assoc($res); $race1 = $race1['COUNT(*)']; //echo "Total Human: ".$race1; $query="SELECT COUNT(*) FROM characters WHERE race=2"; $res = mysql_query($query); $race2 = mysql_fetch_assoc($res); $race2 = $race2['COUNT(*)']; //echo "
Total Orc: ".$race2; $query="SELECT COUNT(*) FROM characters WHERE race=3"; $res = mysql_query($query); $race3 = mysql_fetch_assoc($res); $race3 = $race3['COUNT(*)']; //echo "
Total Dwarf: ".$race3; $query="SELECT COUNT(*) FROM characters WHERE race=4"; $res = mysql_query($query); $race4 = mysql_fetch_assoc($res); $race4 = $race4['COUNT(*)']; //echo "
Total Nightelf: ".$race4; $query="SELECT COUNT(*) FROM characters WHERE race=5"; $res = mysql_query($query); $race5 = mysql_fetch_assoc($res); $race5 = $race5['COUNT(*)']; //echo "
Total Unded: ".$race5; $query="SELECT COUNT(*) FROM characters WHERE race=6"; $res = mysql_query($query); $race6 = mysql_fetch_assoc($res); $race6 = $race6['COUNT(*)']; //echo "
Total Tauren: ".$race6; $query="SELECT COUNT(*) FROM characters WHERE race=7"; $res = mysql_query($query); $race7 = mysql_fetch_assoc($res); $race7 = $race7['COUNT(*)']; //echo "
Total Gnome: ".$race7; $query="SELECT COUNT(*) FROM characters WHERE race=8"; $res = mysql_query($query); $race8 = mysql_fetch_assoc($res); $race8 = $race8['COUNT(*)']; //echo "
Total Troll: ".$race8; $query="SELECT COUNT(*) FROM characters WHERE race=10"; $res = mysql_query($query); $race10 = mysql_fetch_assoc($res); $race10 = $race10['COUNT(*)']; //echo "
Total Bloodelf: ".$race10; $query="SELECT COUNT(*) FROM characters WHERE race=11"; $res = mysql_query($query); $race11 = mysql_fetch_assoc($res); $race11 = $race11['COUNT(*)']; //echo "
Total Draenei: ".$race11; //----------- by class ------------------------------------------------------- //echo "
-----------------Chars Distribution by Class ----------
"; $query="SELECT COUNT(*) FROM characters WHERE class=1"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class1 = $fe['COUNT(*)']; //echo "
Warrior: ".$class1; $query="SELECT COUNT(*) FROM characters WHERE class=2"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class2 = $fe['COUNT(*)']; //echo "
Paladin: ".$class2; $query="SELECT COUNT(*) FROM characters WHERE class=3"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class3 = $fe['COUNT(*)']; //echo "
Hunter: ".$class3; $query="SELECT COUNT(*) FROM characters WHERE class=4"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class4 = $fe['COUNT(*)']; //echo "
Rogue: ".$class4; $query="SELECT COUNT(*) FROM characters WHERE class=5"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class5 = $fe['COUNT(*)']; //echo "
Priest: ".$class5; $query="SELECT COUNT(*) FROM characters WHERE class=6"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class6 = $fe['COUNT(*)']; //echo "
Death Knight: ".$class6; $query="SELECT COUNT(*) FROM characters WHERE class=7"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class7 = $fe['COUNT(*)']; //echo "
Shaman: ".$class7; $query="SELECT COUNT(*) FROM characters WHERE class=8"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class8 = $fe['COUNT(*)']; //echo "
Mage: ".$class8; $query="SELECT COUNT(*) FROM characters WHERE class=9"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class9 = $fe['COUNT(*)']; //echo "
Warlock: ".$class9; $query="SELECT COUNT(*) FROM characters WHERE class=11"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $class11 = $fe['COUNT(*)']; //echo "
Druid: ".$class11; //----------- pets ------------------------------------------------------- //echo "
-----------------Chars Pets ----------
"; $query="SELECT COUNT(*) FROM character_pet"; $res = mysql_query($query); $fe = mysql_fetch_assoc($res); $pets = $fe['COUNT(*)']; //echo "
Total number of pets: ".$pets; $r1=rand(1,24000); $r2=rand(1,24000); $r3=rand(1,24000); $r4=rand(1,24000); $query="SELECT name FROM character_pet WHERE renamed=1 ORDER BY RAND() LIMIT 1"; $res= mysql_query($query); $pet_name_1 = mysql_fetch_assoc($res); $pet_name_1 =$pet_name_1['name']; $query="SELECT name FROM character_pet WHERE renamed=1 ORDER BY RAND() LIMIT 1"; $res= mysql_query($query); $pet_name_2 = mysql_fetch_assoc($res); $pet_name_2 =$pet_name_2['name']; $query="SELECT name FROM character_pet WHERE renamed=1 ORDER BY RAND() LIMIT 1"; $res= mysql_query($query); $pet_name_3 = mysql_fetch_assoc($res); $pet_name_3 =$pet_name_3['name']; $query="SELECT name FROM character_pet WHERE renamed=1 ORDER BY RAND() LIMIT 1"; $res= mysql_query($query); $pet_name_4 = mysql_fetch_assoc($res); $pet_name_4 =$pet_name_4['name']; //echo "
Some random names for pets are: ".$pet_name_1.", ".$pet_name_2.", ".$pet_name_3.", ".$pet_name_4; $content =" #stat{ font-color:#FF00000; font-weight:bold; }

General

Server FUN uptime: $uptime_server1	Total accounts: $total_accounts
Server HIGH uptime: $uptime_server2	New accounts (last 24h): $new_accounts
FUN Server has $total_chars1 chars	HIGH Server has $total_chars2 chars
Total kills: $total_kills	Yesterday kills: $yesterday_kills
One account has an average of $avg_chars chars
Total pets: $pets
Some random pet name: $pet_name_1, $pet_name_2, $pet_name_3, $pet_name_4

Server statistics

Statistics are updated once an hour

"; $filename = '../statistics.txt'; $somecontent = $content; // Let's make sure the file exists and is writable first. if (is_writable($filename)) { // In our example we're opening $filename in append mode. // The file pointer is at the bottom of the file hence // that's where $somecontent will go when we fwrite() it. if (!$handle = fopen($filename, 'w')) { echo "Cannot open file ($filename)"; exit; } // Write $somecontent to our opened file. if (fwrite($handle, $somecontent) === FALSE) { echo "Cannot write to file ($filename)"; exit; } echo "Success, wrote ($somecontent) to file ($filename)"; fclose($handle); } else { echo "The file $filename is not writable"; } //echo $content; ?>[/code][/CODE]

hope you like it /emoticons/default_smile.png

Zze · January 1, 2011, 11:04pm

Nice! /emoticons/default_happy.png

Beethoven_Virus · January 3, 2011, 12:54am

Do you have sample? I so want to see it.

Thanks!

DAS · January 3, 2011, 10:51am

i have, but i prefer not to give the links.

http://www.google.com/images?q=google+graphs&oe=utf-8&rls=org.mozilla:en-US:official&client=firefox-a&um=1&ie=UTF-8&source=og&sa=N&hl=ro&tab=wi&biw=1680&bih=897

marcus · January 3, 2011, 1:14pm

tested and its realy nice /emoticons/default_biggrin.png

javierin · January 4, 2011, 11:52am

I think the way to obtain account and char totals is incorrect.

In older servers, some account or character purge may have been done, i would change

$query="SELECT max(id) from account";

for

$query="SELECT count(*) from account";

Another thing i have observed is character counting, which appears to be wrong.

[CODE]MariaDB [characters]> select count(*) from characters;

±---------+

| count(*) |

±---------+

| 23281 |

±---------+

1 row in set (0.00 sec)

MariaDB [realmd]> SELECT sum(numchars) FROM realmcharacters WHERE realmid=1;

±--------------+

| sum(numchars) |

±--------------+

| 31365 |

±--------------+

1 row in set (0.01 sec)[/CODE]

I don’t know what can be causing this difference in the queries, but in our server, COUNT()=Horde+Alliance as well as COUNT()=Male+Female, so I guess COUNT(*) is the right approach to get the number of characters created.

Another code change proposal is for calculating events in the last 24h. It is nice to do it with php, but mysql can do it alone, and probably faster:

Old:

$query="SELECT COUNT(*) FROM account WHERE joindate>'$acu24h'";

Proposed:

$query="SELECT COUNT(*) FROM account WHERE joindate > DATE_SUB(NOW(), INTERVAL 1 DAY)";

Just my $0.02

Apart from this, great job!

Nightlife · January 4, 2011, 3:02pm

[CODE]

UPDATE authdatabase.realmcharacters SET numchars = (SELECT count(*) FROM chardatabase.characters WHERE realmcharacters.acctid = characters.account);[/CODE]

DAS · January 5, 2011, 2:40pm

yes, you’re right.

those that want to use it should take in consideration your ( javierin + Nightlife ) suggestions.

there are some other “improvements”, like Google Flash graphs which break apart the slices and look MUCH cooler, but i didn’t have the time to look into them.

what’s up with MariaDB ?..

javierin · January 5, 2011, 3:04pm

MariaDB just happens to be the db we use in our server. What i wanted to show is the difference between the character numbers with both queries. I am using the SELECT COUNT(*) query since it eturns the same number as Male characters + Female characters, as well as Horde Characters + Alliance Characters.

It didn’t make much sense to me to have a total number of characters of, let’s say, 100, and then have figures which said that we had 30 Horde and 40 Alliance, which added up didn’t equal Total Chars of 100 (Which is the number initially reported by SELECT sum(numchars) FROM realmcharacters WHERE realmid=1;

I have never used google graphs, but if you ever further experiment with these cooler graph types, i am pretty sure everyone here will love them.

DAS · January 6, 2011, 10:20am

I forgot to mention, it’s necessary to always store those statistics in a file and display them from that file since COUNT(*) in InnoDB tables it’s very slow.

yes, there are like 999 types of google graphs.

this one is nice: http://code.google.com/apis/visualization/documentation/examples.html#custom_table_example

also this one (let’s say online time, horde - alliance) http://dygraphs.com/

and they are really simple to use.

I’ve heard some pretty good things about MariaDB. How it’s managing WOW ?

redhat66 · January 21, 2011, 8:08pm

Greetings and thanks for the post.

I have edited and published the PHP page but the result in my browser is “#!/usr/bin/php The file …/statistics.txt is not writable”.

I have not installed any statistics software so not sure where the txt file is coming from or supposed to be. Am I missing something?

Thanks in advance.

Acid · January 22, 2011, 12:34pm

Very nice ! Thank you.

DAS · January 29, 2011, 9:43am

redhat.

you can delete the #!/usr/bin/php line at the top… you can call the cron by "wget -O /dev/null full_path/to_your_php/file.php

also you need to create the statistics.txt file manually and give it write permissions…

Keifer · January 29, 2011, 2:33pm

Edit

$filename = ‘…/statistics.txt’;

to

$filename = ‘statistics.txt’;

and it worked for it, if statistics.txt is in the same folder as your stats.php

also, can anyone help me out with this syntax error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'char.characters WHERE race IN(2,5,6,8,10)) as horde, (SELECT COUNT(guid) FROM ’ at line 1

I got these stats to work on a previous auth and char database but recently created a new one and it doesn’t seem to want to work.

DAS · February 7, 2011, 12:54am

just replace that query with 2 simpler ones.

SELECT COUNT(*) FROM $CharacterDatabase.characters WHERE race IN(2,5,6,8,10));

$horde=row[‘COUNT(*)’];

SELECT COUNT(*) FROM $CharacterDatabase.characters WHERE race IN(1…));

$ally=row[‘COUNT(*)’];

$total_players = $ally + $horde…

etc…

do note that InnoDB takes a long time to perform a COUNT(*)…