Perl-MySQL get column names

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

Hi monks,

I'm trying to get the columns name of a table. Let's say I've the following sql statement (simplified):

my $sql = qq{ SELECT item1 FROM basket WHERE item LIKE 'a%'
 UNION SELECT item2 FROM basket WHERE item2 LIKE 'a%'
 UNION SELECT item3 FROM basket WHERE item3 LIKE 'a%'
 UNION SELECT item4 FROM basket WHERE item4 LIKE 'a%'
 ORDER BY 1
};
[download]

Somewhere in the code, I've:

while (my @ary = $sth->fetchrow_array()) {
  # $ary[0] gives the value of each selected item
}
[download]

What I would like to do is to be able to tag the column name to the value for that column. For example:

item1  perl
item2  python
item3  ruby
item4  php
[download]

I would like to be able to associate, say, ruby with item3 (the column name).

When I wrote @$sth->{NAME}, all that I got was item1.

Is there a way to relate a value to its column name?

Many thanks in advance :)

Comment on Perl-MySQL get column names Select or Download Code

Replies are listed 'Best First'.
Re: Perl-MySQL get column names by tachyon (Chancellor) on Nov 07, 2004 at 12:04 UTC
Your SQL looks a bit odd. If you mean something like (SELECT item1, item2, item3 FROM basket) then: `$hashref = $sth->fetchrow_hashref;` [download] does what you want with key field name and value...... cheers tachyon	[reply] [d/l]
Re^2: Perl-MySQL get column names by kiat (Vicar) on Nov 07, 2004 at 12:23 UTC
Thanks, tachyon! I've modified my sql. I missed out the LIKE clause.	[reply]
Re: Perl-MySQL get column names by pg (Canon) on Nov 07, 2004 at 20:05 UTC
"What I would like to do is to be able to tag the column name to the value for that column. For example: `item1 perl item2 python item3 ruby item4 php"` [download] For mySQL, you can do it in this way: `SELECT concat('item1 ', item1) FROM basket WHERE item LIKE 'a%' UNION SELECT concat('item2 ', item2) FROM basket WHERE item2 LIKE 'a%' ORDER BY 1` [download] For Oracle, I used to do: `SELECT 'item1 ' \|\| item1 FROM basket WHERE item LIKE 'a%' UNION SELECT 'item2 ' \|\| item2 FROM basket WHERE item2 LIKE 'a%' ORDER BY 1` [download]	[reply] [d/l] [select]
Re^2: Perl-MySQL get column names by Aighearach (Initiate) on Nov 07, 2004 at 20:35 UTC
The Oracle one will work on PostgreSQL, too. I don't have the standards memorized, but it's probably SQL92 compliant, and should be very portable. -- Snazzy tagline here	[reply]
Re^2: Perl-MySQL get column names by kiat (Vicar) on Nov 08, 2004 at 06:27 UTC
Thanks, pg! I've tried it out and yes, I'm able to join the selected value to the column with your sql code using concat. It's what I was looking for. cheers	[reply]
Re: Perl-MySQL get column names by Aighearach (Initiate) on Nov 07, 2004 at 20:30 UTC
Another technique for dealing with "relat(ing) a value to it's column name" is to use `$sth->fetchrow_hashref` [download] This returns an anonymous hash for each row, with the column names as keys. I use this a lot (hashes are the best data type for most things in Perl, anyways). Normally it's bad to `SELECT ` [download] There are obvious performance reasons for retreiving only the data you need, but if you're usually needing most of the columns in a row, then the difference is often slight. The real reason not to `SELECT ` is that when you use `$sth->fetchrow_array` or any other such methods that retrieve the row as a list, then if you change the table schema, you might get your data back in the row order. With none of your code having been changed! But if you use `$sth->fetchrow_hashref` (or `$sth->fetchall_hashref`, etc) then this isn't a problem, because the keys in the hash are the column names. So not only you can safely `SELECT *`, but then when you do, and your table schema has changed, you don't have to modify your SQL. You can either just ignore the new columns, or if you need them, there they are in your hashes. Poof! I like this approach, because when I make changes it Does What I Mean. -- Snazzy tagline here	[reply] [d/l] [select]
Re^2: Perl-MySQL get column names by kiat (Vicar) on Nov 08, 2004 at 06:29 UTC
Thanks, Aighearach! `$sth->fetchrow_hashref` [download] doesn't seem to work because of UNION. I've tried it out but I don't seem to be able to get to the column names - I got choice1 as the column.	[reply] [d/l]