Graphing from ODBC exports - Page 2

EQAditu · 06-04-2009, 01:20 PM

Well, it's correctly setting the content type header now, but there's still an error in the PHP:
Warning: Division by zero in /home2/rogleete/public_html/parse/phpgraphlib.php on line 202

It sends that directly before the PNG data, so browsers don't know what to do with it. I'm not certain what's wrong with it. What you're sending the library looks okay at a glance. It might be worthwhile to see what is coming out of the SQL statement.

Sparks · 06-04-2009, 03:53 PM

I actually sat down and looked at this today. First, change:

PHP Code:


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

To:

PHP Code:


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

For cleanliness. AVG() can return a lot of decimal clutter, which you're probably happy to ignore.

As far as the SQL, you're at least missing a GROUP BY statement, but the error that that should give you would be displaying only 1 person, not none, which is what you're getting. So try changing = 'All' to != 'All' instead, and see what happens... Like:

PHP Code:


			
$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)
     GROUP BY b.name
     ORDER BY AVG(b.extdps) DESC LIMIT " . $count;

If that doesn't work, change this line:

PHP Code:


			
$result = mysql_query($query);

to:

PHP Code:


			
$result = mysql_query($query)
     or die('Query failed: ' . mysql_error());

If that doesn't work (or at least generate a useable error), change:

PHP Code:


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

To:

PHP Code:


			
while($row = mysql_fetch_row($result))
{
          $name = $row[0];
          echo $name . " ";
          $extdps = intval($row[1]);
          echo $extdps . " <br />";
          $data[$name] = $extdps;
}

And make sure that you're actually getting data out. Which should look like:
NAME1 AVGDPS1
NAME2 AVGDPS2
etc.

EDIT

For reference, this works fine for me:

PHP Code:


			
<?php

include("phpgraphlib/phpgraphlib.php");

$host = "localhost";
$database = "act";
$password = "bbq";

$link = mysql_connect($host, $database, $password)
     or die('Could not connect: ' . mysql_error());
     
mysql_select_db('act') or die('Could not select database');

//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)
    GROUP BY b.name
    ORDER BY AVG(b.extdps) DESC LIMIT " . $count;

$result = mysql_query($query)
     or die('Query failed: ' . mysql_error());

$data = array();

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


$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");
$graph->setDataValues(true);
$graph->setDataValueColor("200,0,0", "100,0,0");
$graph->setBarOutline(false);
$graph->setBackgroundColor("11,13,13");
$graph->setTextColor("white");
$graph->setLegend(false);
$graph->createGraph();

?>

Sparks · 06-04-2009, 05:11 PM

Come to think of it, you probably want this:

PHP Code:


			
<?php

include("phpgraphlib/phpgraphlib.php");

$host = "localhost";
$database = "act";
$password = "bbq";

$link = mysql_connect($host, $database, $password)
     or die('Could not connect: ' . mysql_error());
     
mysql_select_db('act') or die('Could not select database');

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

//validate $count
is_int($count)
    or die('Error: Count has to be a valid integer.');

//days to look into the past
if(!isset($_GET['days']))
    $days = 7;
else
    $days = mysql_real_escape_string( $_GET['days'] );

//validate $days
is_numeric($days)
    or die('Error: Days has to be a valid number.');

//extdps or exthps?
if(!isset($_GET['type']))
    $type = "extdps";
else
    $type = strtolower(mysql_real_escape_string( $_GET['type'] ));

//validate $type
if(!preg_match('/\bext[dh]ps\b/',$type))
    die('Error: Type must be either EXTDPS or EXTHPS');

$query = "SELECT b.name, AVG(b." . $type . ")
    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 '" . $days . "' DAY)
    GROUP BY b.name
    ORDER BY AVG(b." . $type . ") DESC LIMIT " . $count;

$result = mysql_query($query)
    or die('Query failed: ' . mysql_error());

$data = array();

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


$title = "Top " . $count . " "
    . strtoupper(substr($type,3,1))
    . "PSers from the last " . $days . " 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");
$graph->setDataValues(true);
$graph->setDataValueColor("200,0,0", "100,0,0");
$graph->setBarOutline(false);
$graph->setBackgroundColor("11,13,13");
$graph->setTextColor("white");
$graph->setLegend(false);
$graph->createGraph();

?>

And that way, you can just use things like:

Code:

http://parse.reclamationeq2.com/graph_weekly_dps.php?type=exthps&count=5

when you want to flip to a 5 man heal parse.

Or do a 90 day parse instead:

Code:

http://parse.reclamationeq2.com/graph_weekly_dps.php?days=90

etc.

edit for truncated links.

Spydie · 06-04-2009, 07:00 PM

Yer fuckin awesome man, worked like a damn charm!!

Spydie · 06-04-2009, 07:09 PM

I didn't do the second one, because I want to be able to have the heal parse one to be green not red.
Otherwise you are a champ for helping me out. You and EQAditu, thank you both very much!

Sparks · 06-04-2009, 07:26 PM

Quote:

Originally Posted by Spydie

I didn't do the second one, because I want to be able to have the heal parse one to be green not red.
Otherwise you are a champ for helping me out. You and EQAditu, thank you both very much!

Just in case, now with color switching based on type. I hate maintaining more than one file, personally.

Change the last bit to:

PHP Code:


			
$graph = new PHPGraphLib(800,300);

$graph->addData($data);
$graph->setGrid(false);
$graph->setTitle($title);
$graph->setTitleColor("white");
$graph->setupXAxis(28, "white");
$graph->setupyAxis("", "white");
($type == 'exthps') ? $graph->setGradient("lime", "green") : $graph->setGradient("red", "maroon");
$graph->setDataValues(true);
($type == 'exthps') ? $graph->setDataValueColor("lime") : $graph->setDataValueColor("red");
$graph->setBarOutline(false);
$graph->setBackgroundColor("11,13,13");
$graph->setTextColor("white");
$graph->setLegend(false);
$graph->createGraph();

Spydie · 06-04-2009, 08:16 PM

once again, yer the shit!

Spydie · 06-04-2009, 08:36 PM

got it all changed, thanks again!

Spydie · 06-06-2009, 09:34 PM

nevermind...

Spydie · 06-19-2009, 08:06 PM

got them both added to one script...

this is everything wrapped up into one zip file

Download