GD Graph & MySQL

[lynxus]

Hey guys, Im having a nightmare of a problem.

I have a fairly large database and need to generate some useful graphs at the end of each month via a crontab.

I cannot for the life of me figure out how to get dynamic data from sql to a GD graph and then save the graph as a .png etc for use later.

Im trying to do something like below.. If anyone knows how to do this please either post the code or point me in the direction of how to do this. Only got a week left to get this sorted!

--
I have a database with many values, Im really only interested in rows created in the passed month.

So what i want is to SELECT all rows that have been created last month ( From teh datecreated ) feild and then count how many have a overallpercent done > 99 LIKE BELOW:

elect user, COUNT(overalldone) from data where overalldone > 99 group by user;


Then basically chuck this in a bar graph.
So it shows what user has a higher amount of completed tasks against how many they have actually created..

I hope this makes sence!


Cheers
G

 

[stevexff]

Use the DBI module for database access.

http://search.cpan.org/~timb/DBI-1.52/DBI.pm#___top

Something like (untested)

use strict;
use warnings;
use DBI;
use Data::Dumper;

my $cutoff = "2006-09-30";

my ($dsn, $user, $password) = ("name", "me", "mypwd");

my $dbh = DBI->connect($dsn, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 }) or die "Can't connect! $DBI::errstr";

my $sql = "SELECT user, COUNT(*) FROM data WHERE overalldone > 99 AND datecomplete > ? GROUP BY USER";

my $stuff = $dbh->selectall_arrayref($sql,,$cutoff);

$dbh->disconnect;

print Dumper($stuff); # you get to interface it with GD yourself...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]