thuperuser has asked for the wisdom of the Perl Monks concerning the following question:

I am running a MySQL query and trying to output the data into column format. The data itself comes from a MySQL database, and then I have an array of data labels that I hard-coded (@array) that the database data needs to match up with. 

my $counter = 0;

while (@ary = $sth->fetchrow_array ())
{
    if ($ary[$counter] == NULL){
     print join("\t",@array[$counter]), " ","\n";
     $counter++;
    }
    else
    {
    print join("\t",@array[$counter]), $ary[$counter],"\n";
    }

    $counter++;

}

[download]

The problem seems to be multi-fold. First, my MySQL query is returning 10 rows when I need just one row (I realize this is not a question for the Perl monks, but I promise the next one is).

The main issue is that the while iteration is only running 10 times (the number of rows that fetchrow_array is retrieving) and there are more than 10 possible data labels that are to be printed in the left column of the data output. Some of the data will not yet be in the database (and it is not at the end of the data query but rather in some of the middle fields), so I tried to skip the null data fields by incrementing count, but then the problem still remains that the while loop is not going to retrieve the last several data fields to output because its already iterated 10 times and there are still several more data fields in array beyond that. I need to know how to print all labels in my hardcoded @array and also retrieve all possible data fields from the MSQL query so that for each possible data field there is a label printed to the left, regardless of whether there is data to the right of it.

Thanks in advance for any help.

Replies are listed 'Best First'.
Re: formatting output from a mysql query in column format
by NetWallah (Canon) on Jul 25, 2011 at 18:19 UTC
    It looks like you are missing a loop to iterate over all the COLUMNS returned from fetchrow_array.

    You appear to be using the $counter - both as a row index, and as a column index.

    Is there a reason you use the array slice syntax "@array[$counter]" to extract what appears to be a single item ?

    "LIMIT 1" can be appended to your SQL - if you just want one row returned. There are other ways to get SQL to return the desired row - but you have not indicated what filters you require.

                "XML is like violence: if it doesn't solve your problem, use more."

[reply]

      It looks like you are missing a loop to iterate over all the COLUMNS returned from fetchrow_array.

      I probably am -- how can I implement this within the code as it is?

      Is there a reason you use the array slice syntax "@array$counter" to extract what appears to be a single item ?

      Because the labels in the @array need to match up with the fields output by MySQL...also I am completely new to perl programming, so correct me if I am making some egregious error there (I don't count running perl scripts as experience).

[reply]
        If I understand what you are trying to do - you are spinning your wheels too hard.

        Let the perl modules do the work for you.

        Here is what I think you are trying to get to: 

         # @array contains the field names (You call them 'labels') .
 # OK - so I assume they are also the field names, and we will use the
+m as labels.
my $counter = 0;

while ( my $r =  $sth->fetchrow_hashref() )
{
   $counter ++; 
   print " ---- $counter ------\n";
   for my $fieldname (@array){
     print " $fieldname:\t" . $r->{$fieldname} . "\n";
   }
}

        [download]
        (Untested)

                    "XML is like violence: if it doesn't solve your problem, use more."

[reply]
[d/l]
Re: formatting output from a mysql query in column format
by kcott (Archbishop) on Jul 26, 2011 at 00:20 UTC

    Here's an example of the type of thing you could do in this situation: 

    use strict;
use warnings;

my @labels = qw{First: Middle: Last: Age:};
my @sql_data = (q{John}, q{}, q{Smith}, 20);
my $no_value = q{*NO_VALUE_SUPPLIED*};

my $counter = 0;

print map {
    ($labels[$counter++], qq{\t}, ($_ ne q{} ? $_ : $no_value), qq{\n}
+)
} @sql_data;

    [download]

    Which produces: 

    First:  John
Middle: *NO_VALUE_SUPPLIED*
Last:   Smith
Age:    20

    [download]

    As you're new to Perl, here's a few pointers:

    • Always include use strict; and use warnings; at the start of your code
    • There are different operators for comparing numbers and strings - see perlop (in particular, the sections Relational Operators and Equality Operators)
    • Choose more meaningful variable names (@ary and @array aren't particularly enlightening)

    -- Ken

[reply]
[d/l]
[select]
Re: formatting output from a mysql query in column format
by onelesd (Pilgrim) on Jul 26, 2011 at 06:29 UTC

    You could use printf for this.

    ...
printf "Label-1:\t%s\n", $ary[0] ;
printf "Label-2:\t%s\n", $ary[1] ;
...

    [download]

    I should also mention your post is confusing. You would get better help from us by describing what you are trying to do, including a sample of your data, and then separately describing the problem you are encountering.

[reply]
[d/l]

Back to Seekers of Perl Wisdom