Skip to content

PHPlot, MySQL and the Dark Ages of Camelot

The Dark Ages of Camelot (DAoC) is one of a number of Massive Multiplayer Online Role playing games (MMORPG) available for people who like those types of things. Mythic Entertainment, the company behind DAoC decided to offer an xml feed with various statistics about the game including the number of active players. From April 2002 to July 2005 I used a cron job to query the xml feed once every 5 minutes, parse the results and store the statistics in a database table.

I made a graph of these statistics available on a fan site I operated. The graph used the open source PHPlot graphing class in order to draw a line graph charting the number of players playing the game by hour for a 24 hour period. Visitors to the site would see the line graph shown here, plotting the number of players per hour against a second line showing the day's average. Like many PHP graphing libraries, PHPlot assumes that you have the GD library installed and available to PHP on order for the graphs to work.

This was one of my early experiments with PHP, and illustrated its power and flexibility. Now that the site where the graph appeared is no longer online, I figured I would provide the code, and a small subset of the database, for people interested in how to program with PHPlot. At the time I created this code, PHPlot didn't have much documentation and required some trial and error experimentation. Of course, since then (several years now) PHPlot has been improved, and should definately be considered if you want graphing in your application. Because I don't want to spend time debugging old obsolete code, I've provided the library I used in the download along with the graph script. It would probably work with the latest release, but I don't have time to test it. You can download my example database, version of PHPlot and script here. The database contains one months data, so you can play with the script and explore the class.

Read on for some brief instructions and an explanation of the code.
Installing the database
You'll need a mysql database to install the script. If you have access to the mysql command line, you can do this from a shell like so:

mysql -u username -p yourdatabase < daocusers_april.sql


You can also use phpMyAdmin to install the script. The script will create the table daoc_rvrstats and load the data for the month of April 2004.

Editting the script
You'll need to edit the script to provide the appropriate database credentials. You probably will be using localhost, but the user, password and database name will need to be changed to match your environment. Find the following lines in the daocusergraph.php file and edit them.

//mysql connectivity stuff
//change to reflect your database
//
$mysqlhostname = 'localhost';
$mysqlusername = 'changeuser';
$mysqlpasswd = 'changepw';
$dbnme = 'changedb';
//


A quick test.
If you've installed the database table, and made the appropriate changes, you should be able to do a quick test by calling the script:

http://www.yoursite.com/path-to-script/daocusergraph.php

If things are working, you should get a random day from the month. There is a bug that means you have a 1 in 31 chance of getting garbage. If you see some info that indicates there was a divide by zero, refresh the page again once or twice before you give up.

Things to play with
You should note that the script takes a few url parameters. Experiment with these:

height=pixels
width=pixels
daocdate=YYYY-MM-DD (date)
inline=1 (turn off embedded image header, so you can emit your own header data if you want).

You will note that when you provide a date, you always get the previous day's data. This was the design of the system, and is hardwired into the queries (and the source of the bug I mentioned earlier.) Obviously the queries could be modified to show the data for the date specified.

Loading the data
Since I wanted a line graph, the main trick was figuring out what phplot wanted. This is the code that does most of the work:


        $sql1 = "select HOUR(created) as created, population / 1000 as pop1k
        FROM daoc_rvrstats
        WHERE
        created > DATE_ADD(DATE_FORMAT("
. $daocdate . ", '%Y-%m-%d'), INTERVAL -1 DAY) AND
        created < DATE_FORMAT("
. $daocdate . ", '%Y-%m-%d')
        ORDER BY created"
;


        $rslt1 = @mysql_query($sql1, $dbh1) or die("Couldn't execute select:".@mysql_error());         
        $hr = "-1";
        while($row = mysql_fetch_row($rslt1)) {
               
                if ($row[0] != $hr)     {
                        $hr = $row[0];
                } else {
                        $row[0] = '';
                }
                //Add the average to the graph
                $row = array_pad($row, 3, $sumrow["avgpop"]/1000);
                $graph_data[] = $row;          
        }
 


As you can see, the query selects all the rows from the database for the prior day, returning the HOUR() number, and the number of players divided by 1000 for scaling purposes. This works very well because the created column in the database is a DATETIME type, and HOUR() is all that's needed to get an hour number from 0-23. The trick as it turns out, is that PHPlot expects an array with a series of dimensional pairs. The first dimension is the X axis, and the 2nd dimension is the Y. Whenever a value changes in the first array element, PHPlot considers this a new subseries. It should be evident hopefully that what the inner logic statement does, is check to see if the hour has changed. If it hasn't changed, the hr value from the result set is NULLED out. Figuring this out was probably the trickiest part of getting the line graph to work. Because the average is precomputed in an initial query, adding the average line was as simple as grafting on a new pair of values onto the $row array using array_pad.


Making the Graph
Actually calling the graph was relatively simple, and the function oriented documentation is probably enough for you to figure out the basics. Again the tricky part was figuring out what arrays to create in preparation for creating the PHPlot object.


$legend = array('users', 'average');
$linecolors = array('navy', 'SkyBlue');
$bordercolor = array('black', 'black');

//Define the object
$graph = new PHPlot($ws,$hs);
$graph->SetFileFormat("jpg");
//This let's me embed this as a img call.
$graph->SetIsInline($inline);
$graph->SetBackgroundColor("white");
$graph->SetPlotType("lines");

//Set some data
$graph->SetDataValues($graph_data);
$graph->SetDataColors($linecolors,$bordercolor);
$graph->SetTitleColor("SlateBlue");
$graph->SetTitle($daoctitle);
$graph->SetXLabel("Hour(PST)");
$graph->SetYLabel("Number of 1k users");
$graph->SetLegend($legend);
$graph->SetLegendPixels(25,25,1);

//Draw it
$graph->DrawGraph();
 


As you can see, the constructor takes a length and width parameter and PHPlot scales the size of the graph based on those parameters. Although the graph defaults to 640x480, I found that a graph as small as 480x320 was still very legible. Try daocusergraph.php?width=480&height=320 and see how it works for you.

I set up a Legend Array in advance, and PHPlot is smart enough to know that this means there will be two lines to plot, and to use the appropriate array element for each. Once we tell it we want a line graph using the SetPlotType("lines") method, and load the data using SetDataValues($graph_data) the rest of the work, is primarily cosmetic, setting up a legend, and telling PHPlot what colors to use.

Hopefully this introduction shows that graphing with a php graph library like PHPlot is not difficult, and in a few lines of code you can add sophisticated looking dynamically generated graphs to your website.



Defined tags for this entry: , , , , ,

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

DAOC Plat on :

*Thanks a lot for your code. We are going to visualize our website statistics using gd. Our site is related to MMORPGs as well. I assume your code will make our life a bit easier ;) Thanks again.

kun on :

*Thanks a lot for your code. But i have an error after install the code. So i hope you can give advice how to settle the error.
The error is :
Fatal error: Call to undefined function: imagecreate() in c:\apache\htdocs\phplotexample\phplot\phplot.php on line 203

Thanks You.

David on :

*The problem you encountered has to do with your lack of GD support on your server and/or PHP. PHPlot depends on it.

fenderflip on :

*Hi, thanks for being one of the few trying to explain how to use MySql with PhPlot. I'm trying to pull a simple query from my database and feed it into PhPlot, but the syntax is killing me. The query is as such:

while($ageCount

Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
BBCode format allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
Form options