1

I have a Dynamic Pivot in MySQL (see this question: MySQL "Multi-Dimensional?" Dynamic Pivot)

I want to know the Column/As/Field names as if it were a table and I queried INFORMATION_SCHEMA (which if this was a REAL table, would be easy enough: MySQL query to get column names?).

But, I can find no question or reference to a function or SELECT option to get the Column/As/Field names from a query. Is this possible? If so, how?

Using Perl to access MySQL (http://dbi.perl.org/).

Community
  • 1
  • 1
RedSands
  • 145
  • 1
  • 14
  • How about using a temporary table? Just an idea, I'm not sure if the `INFORMATION_SCHEMA` will pick that up – KaeL Aug 20 '15 at 01:13
  • What host language are you using? (Java? PHP?) most host SQL interfaces have a way to find out column names from the resultset of a query. – O. Jones Aug 20 '15 at 01:18
  • @OllieJones I think you're on to something! Looking deeper into: fetchrow_array(). – RedSands Aug 20 '15 at 01:30

2 Answers2

0

So, flipping this around... we know the fixed columns. So, if we use the same basic query that creates the Pivot to begin with, we can get a GROUP_CONCAT of the names.

SET @sql = NULL;

SELECT GROUP_CONCAT(qrv.req_name) INTO @sql
    FROM (SELECT qrt.req_name FROM qual_requirment_values qrv JOIN qual_requirment_types qrt ON qrt.id = qrv.req_type_id) qrv;

SET @sql = CONCAT('r.rank,r.member_type,im.name,qrv.grouping,', @sql);

SELECT @sql;

This can then be split into an array and used.

Seems like the long way around, but in the absence of something else it will work for my application. Seems horribly inefficient! :)

RedSands
  • 145
  • 1
  • 14
0

The better answer, thanks to @OllieJones. The Data Base Interface used to access MySQL should provide a way.

In my case (Perl), the answer is here: http://www.perlmonks.org/?node_id=264623

my $sql = ... [some query];
my $sth = $dbh->prepare($sql);
$sth->execute();
my $field_name_arrayref = $sth->{NAME};

Further to the answer, this is the full method within my MySQL package. do() is our generic DBI method that returns queries in an AoA. Adapting that method to create do_fieldNames();

## Tested Method
sub do_fieldNames {
    my ($self, $sql, $has_results) = @_;
    my ($sth, $rv, @row, @query_results);

    ## Execute the SQL statement
    $sth = $$self->prepare($sql);
    $rv = $sth->execute or &error(3306, __LINE__, __FILE__, $sql, $$self->errstr);
    return undef unless $rv > 0;

    ## SOLUTION >> Field Name arrayref, part of the standard included DBI Perl Module
    my $field_name_arrayref = $sth->{NAME};

    ## Parse the results
    if ($has_results || $sql =~ /^select/i) { 
        while (@row = $sth->fetchrow_array) {
            push @query_results, [ @row ];      
        }
    }

    ## Return results
    return (\@query_results, $field_name_arrayref) ;

}
RedSands
  • 145
  • 1
  • 14