Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Parsing an SQL table using an array of hashes?

by chareTX (Initiate)
on Jun 28, 2013 at 20:32 UTC ( #1041338=perlquestion: print w/replies, xml ) Need Help??
chareTX has asked for the wisdom of the Perl Monks concerning the following question:

I am just not sure how best to handle this. I have data that is returned through a mysql query that looks like this:

| MONTH(CreationDate) | YEAR(CreationDate) | ProblemCategory                    | count(*) |
|                   3 |               2013 |  Address               |        1 |
|                   2 |               2013 | Equipment                       |       13 |
|                   3 |               2013 | Equipment                       |       18 |
|                   4 |               2013 | Equipment                       |       17 |
|                   5 |               2013 | Equipment                       |        8 |
|                   3 |               2013 | Database Reconciliation            |        3 |
|                   5 |               2013 | Database Reconciliation            |        3 |
|                   2 |               2013 | Design/Process                     |      123 |
|                   3 |               2013 | Design/Process                     |       74 |
|                   4 |               2013 | Design/Process                     |       42 |
|                   5 |               2013 | Design/Process                     |       30 |

I am trying to build the data for a graph created using GD::Graph, where the x-axis is the problem category and there are multiple bars showing the number of

occurrences for each month within that category. I was thinking of using a hash, like

$y_data{$problem} = ????

where the ??? would be the data for each of the months. the problem I am having is that I have to store the month -- because i have to have the same number of entries for each series for GD::Graph to create the chart -- and include the actual value for the series. Basically, the output I want is a multi-bar group for each category, or to change things use the month on the x-axis where each bar will represent the number of occurrences of the problem.

I am just plain stuck on how to best do this.

Replies are listed 'Best First'.
Re: Parsing an SQL table using an array of hashes?
by LanX (Chancellor) on Jun 28, 2013 at 20:54 UTC
    An appropriate data structure would be


    you could also go for


    Now what is the real question?

    How to get the input from SQL or how to get the output to GD::Graph ?

    Cheers Rolf

    (addicted to the Perl Programming Language)

      well, I was going to iterate through the data from SQL - I can already retrieve that. I was just confused on how to store and then retrieve the data. And yes, the next part of the problem will be how to arrange the data into the various series to give to GD::Graph.

        If you are likely to plot across years or over a period of say the last x months, then it may be convenient to convert your year and months into a 'period number'. Then you can store the data in a hash like this ;


        Storing a string of month/year in another array using the same period numbers as the index makes it easy to build the first record of your plot data.

        Here is an example of what I mean with your data expanded into 2014.

        #perl use strict; use GD::Graph; use GD::Graph::bars; my $YEAR1 = 2013; my @period=(); my %y_data=(); my @x_legend=(); # stroe data while (<DATA>){ chomp; my ($m,$y,$cat,$n) = split ',',$_; # convert year/month to period number my $pd = ym_to_pd($y,$m); $period[$pd] = "$m/$y"; $y_data{$cat}[$pd] = $n; } # graph data my @y_plotdata; push @y_plotdata,[@period]; for my $cat (sort keys %y_data){ push @x_legend,$cat; push @y_plotdata,[@{$y_data{$cat}}]; } # create graph my $my_graph = GD::Graph::bars->new(800,600); $my_graph->set( x_label => 'X Label', y_label => 'Y label', title => 'Title', ); $my_graph->set_legend(@x_legend); my $img = $my_graph->plot(\@y_plotdata) or die $my_graph->error; # save image open(IMG,'>','plot.gif') or die "$!"; binmode IMG; print IMG $img->png; close IMG; # convert year,month to period sub ym_to_pd { my ($yr,$mth) = @_; return ($yr - $YEAR1) * 12 + $mth; } # convert period to year.month sub pd_to_ym { my $ix = shift; my $yr = int(($ix-1)/12)+$YEAR1; my $mth = $ix-12*($yr-$YEAR1); return ($yr,$mth); } #test_periods(); sub test_periods { for my $y (2013..2015){ for my $m (1..12){ my $pd = ym_to_pd($y,$m); my ($yr,$mth) = pd_to_ym($pd); print "$y $m => $pd => $yr $mth\n"; } } } __DATA__ 3,2013,Address,1 2,2013,Equipment,13 3,2013,Equipment,18 4,2013,Equipment,17 5,2013,Equipment,8 3,2013,Database Reconciliation,3 5,2013,Database Reconciliation,3 2,2013,Design/Process,123 3,2013,Design/Process,74 4,2013,Design/Process,42 5,2013,Design/Process,30 1,2014,Design/Process,30
        I never used GD::Graph, but if you need help you should

        a) read the docs / tutorials /search results

        b) show what you already tried

        c) identify your problems

        d) ask concrete questions, instead for complete solutions.

        We love teaching how to fish ... =)

        Cheers Rolf

        ( addicted to the Perl Programming Language)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1041338]
Approved by hdb
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2017-01-22 07:29 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (186 votes). Check out past polls.