Graphing from ODBC exports

Garth · 03-24-2008, 11:17 PM

Quote:

Originally Posted by Pinski

Anyway you'd be willing to part with your code to allow others to host their own site and/or tweak the code to their own liking?

I got a little frustrated with screen shotting parses for posting today so here's part of my solution.

Assuming that you can grok the included MySQL PHP Templates in ACT that Aditu was so kind to provide and that you grok how to do ODBC exports to MySQL, I stumbled across a simple (and free) graphing solution for making parses pretty. Like this (names changed to Anon to protect the innocent!):

The graphing package used is available here: PHPGraphLib Lightweight PHP Graphing Library

The files should be extracted in the same location as the sample ACT files (or you'll need to change the path). A new file should be created named combatant_graph.php (for consistency, though truly, the name doesn't matter). The combatant_graph.php creates a png image, so in order to display it, you need to use image tags. In other words, if you wanted it to show here:

HTML Code:

[img]http://www.yoursite.com/path/to/ACT/files/combatant_graph.php[/img]

Note that the above will simply graph the latest parse in your database. In order to do a specific encounter with a restricted number of particpants, it would be something like:

HTML Code:

[img]http://www.yoursite.com/path/to/ACT/files/combatant_graph.php?encid=12345678&count=6[/img]

The file is set up to grab and graph the latest exported parse if you don't specify an encounter ID (encid). The encid is the same that ACT uses.

The actual content of the file (combatant_graph.php) that generates the image:

PHP Code:


			
<?php

include 'actdb.php';
include 'phpgraphlib.php';

//If encid is not specified, get latest encounter.
if(!isset($_GET['encid']))
{
    $query = "SELECT encid FROM encounter_table ORDER BY endtime DESC LIMIT 1";
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    $encid = $row[0];
}
else
    $encid = mysql_real_escape_string( $_GET['encid'] );

//count is number of people to be graphed. Usually doesn't need to be used unless there are oddities with ally detection.
if(!isset($_GET['count']))
    $count = 24;
else
    $count = mysql_real_escape_string( $_GET['count'] );

$query = "SELECT name,extdps,exthps FROM combatant_table WHERE encid = '$encid' AND ally = 'T'";
$query .= " ORDER BY extdps + exthps DESC";
$query .= " LIMIT $count";

$result = mysql_query($query);

$data = array();
$data2 = array();
$data3 = array();

while($row = mysql_fetch_row($result))
{
    $name = $row[0];
    $extdps = $row[1];
    $exthps = $row[2];
    $data[$name] = $extdps;
    $data2[$name] = $exthps;
    $data3[$name] = $exthps + $extdps;
}

$query = "SELECT title,starttime FROM encounter_table WHERE encid = '$encid' LIMIT 1";

$result = mysql_query($query);
$row = mysql_fetch_row($result);
$title = $row[0] . " " . substr($row[1],0,10);

$graph = new PHPGraphLib(800,300);
$graph->addData($data,$data2,$data3);
$graph->setTitle($title);
$graph->setTitleColor("white");
$graph->setupXAxis(28,"white");
$graph->setupyAxis("","white");
$graph->setGradient("200,0,0", "100,0,0", "0,200,0", "0,100,0", "64,64,64", "black");
$graph->setBarOutline(false);
$graph->setBackgroundColor("122,119,114");
$graph->setTextColor("white");
$graph->setLegend(true);
$graph->setLegendTitle("Damage", "Healed", "Total");
$graph->setLegendColor("114,122,122");
$graph->setLegendTextColor("white");
$graph->createGraph();

?>

I'll probably clean this up a bit more over the next few days, if anyone is interested or needs a feature or what have you.

rtwodeetwo · 03-25-2008, 08:26 AM

Your welcome to registar at EQ2.ca Real Life Quest and try implementing your graphing solution with the code that is working although it already has a working graphing solution courtesy of Coerelius of Kithicor.

Spydie · 06-02-2009, 10:41 PM

there anyway to do this by weekly dps or heals?
would like the title on the graph to show what dates of what it's parsing not the encounter name.

EQAditu · 06-03-2009, 09:45 AM

If you modify the SQL query enough, you can have it make a table/graph of an entire week, yes.

Take this for example...
SELECT b.name, AVG(b.extdps), AVG(b.exthps) FROM encounter_table AS a, combatant_table AS b WHERE a.encid = b.encid AND b.ally = 'T' AND a.title = 'All' AND a.starttime > DATE_SUB(NOW(), INTERVAL '7' DAY) GROUP BY b.name ORDER BY b.extdps + b.exthps DESC

That should combine and average the EXTDPS/EXTHPS of the past 7 days worth of encounters marked, 'All'. If you don't export zone overviews, I guess you can edit the SQL for that.

Changing the script to create a title saying what the date ranges of the last seven days are should be easy to do exclusively in PHP.

Spydie · 06-03-2009, 06:04 PM

I don't know sql queries for the life of me, so could ya help out a bit more? lol
Mainly I don't understand the b.name , b.extdps and all the a. and b. items in that query...
Mainly wanting it to do top 8 DPSers or top 5 Healers for the last 7 days, so yea...

Sparks · 06-03-2009, 07:15 PM

combatant_table AS b

Roughly means: treat anything that says b as combatant_table. Ergo, b is combatant_table in this case; so b.name is combatant_table.name. Using b.name isn't necessary it's just shorthand.

So if you wanted the top 8 dps over the last 7 days (without the AS statements), then:

SELECT combatant_table.name, AVG(combatant_table.extdps) FROM encounter_table, combatant_table WHERE encounter_table.encid = combatant_table.encid AND combatant_table.ally = 'T' AND encounter_table.title = 'All' AND encounter_table.starttime > DATE_SUB(NOW(), INTERVAL '7' DAY) GROUP BY combatant_table.name ORDER BY combatant_table.extdps DESC LIMIT 8

Spydie · 06-03-2009, 07:54 PM

grr I'm gettin all kinds of errors with it this way

http://parse.reclamationeq2.com/graph_weekly_dps.php

Spydie · 06-03-2009, 07:55 PM

here is the whole coding for the file...

Code:

<?php

include 'actdb.php';
include 'phpgraphlib.php';

//If encid is not specified, get latest encounter.
if(!isset($_GET['encid']))
{
    $query = "SELECT encid FROM encounter_table ORDER BY endtime DESC LIMIT 1";
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    $encid = $row[0];
}
else
    $encid = mysql_real_escape_string( $_GET['encid'] );

//count is number of people to be graphed. Usually doesn't need to be used unless there are oddities with ally detection.
if(!isset($_GET['count']))
    $count = 8;
else
    $count = mysql_real_escape_string( $_GET['count'] );

$query = "SELECT b.name, AVG(extdps) FROM encounter_table AS a, combatant_table AS b WHERE a.encid = b.encid AND b.ally = 'T' AND a.title = 'All' AND a.starttime > DATE_SUB(NOW(), INTERVAL '7' DAY) ORDER BY b.extdps DESC LIMIT $count";

$result = mysql_query($query);

$data = array();

while($row = mysql_fetch_row($result))
{
    $name = $row[0];
    $extdps = $row[1];
    $data[$name] = $extdps;
}

$result = mysql_query($query);
$row = mysql_fetch_row($result);
$title = "Top 8 DPSers from the last 7 days";

$graph = new PHPGraphLib(800,300);
$graph->addData($data);
$graph->setGrid(false);
$graph->setTitle($title);
$graph->setTitleColor("white");
$graph->setupXAxis(28,"white");
$graph->setupyAxis("","white");
$graph->setGradient("200,0,0", "100,0,0", "black");
$graph->setDataValues(true);
$graph->setDataValueColor("200,0,0", "100,0,0", "black");
$graph->setBarOutline(false);
$graph->setBackgroundColor("11,13,13");
$graph->setTextColor("white");
$graph->setLegend(false);
$graph->setLegendTitle("DPS");
$graph->setLegendColor("114,122,122");
$graph->setLegendTextColor("white");
$graph->createGraph();

?>

EQAditu · 06-03-2009, 11:18 PM

The section about the encid being checked at the beginning is probably not needed any longer as the graph is generated by date.

Anyhow, the problem looks to be that AVG(extdps) doesn't reference which table the column belongs to and both tables have that column. My original SQL mentioned it as AVG(b.extdps).

Spydie · 06-04-2009, 07:50 AM

Aight fixed it, but now it just says it contains errors and won't generate the graph.
I'm so confused.

http://parse.reclamationeq2.com/graph_weekly_dps.php

Code:

<?php

include 'actdb.php';
include 'phpgraphlib.php';

//count the number of people to be graphed.
if(!isset($_GET['count']))
    $count = 8;
else
    $count = mysql_real_escape_string( $_GET['count'] );

$query = "SELECT b.name,AVG(b.extdps) FROM encounter_table AS a,combatant_table AS b WHERE a.encid = b.encid AND b.ally = 'T' AND a.title = 'All' AND a.starttime > DATE_SUB(NOW(),INTERVAL '7' DAY) ORDER BY b.extdps DESC LIMIT $count";
$result = mysql_query($query);

$data = array();

while($row = mysql_fetch_row($result))
{
    $name = $row[0];
    $extdps = $row[1];
    $data[$name] = $extdps;
}

$result = mysql_query($query);
$row = mysql_fetch_row($result);
$title = "Top 8 DPSers from the last 7 days";

$graph = new PHPGraphLib(800,300);
$graph->addData($data);
$graph->setGrid(false);
$graph->setTitle($title);
$graph->setTitleColor("white");
$graph->setupXAxis(28,"white");
$graph->setupyAxis("","white");
$graph->setGradient("200,0,0", "100,0,0", "black");
$graph->setDataValues(true);
$graph->setDataValueColor("200,0,0", "100,0,0", "black");
$graph->setBarOutline(false);
$graph->setBackgroundColor("11,13,13");
$graph->setTextColor("white");
$graph->setLegend(false);
$graph->createGraph();

?>