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

How do you get DBI (mysql, specifically) fetchall_arrayref({}) to give you both the table AND column name from a SELECT. It always seems to use only the column names, even if you explicit ask for specific "table.column"s. $sth->{NAME*} does not have the originating table for each column either. $dbh->{FetchHashKeyName} might be useful. I must know the truth! See example below. Thanks, 

use DBI;
use Data::Dumper;

my $dbh = DBI->connect(
               'DBI:mysql:database=test;host=localhost', 
               'test', 
               'test',
               { RaiseError => 1 }
               );

sub init
{
  # $dbh->do("DROP TABLE t;");
  $dbh->do("CREATE TABLE t (id INT, x INT, name CHAR(2));");
  $dbh->do("INSERT INTO t (id, x, name) VALUES (1, 0, 'a'), (2, 1, 'b'
+), (3, 1, 'c'), (4, 2, 'd');");
}


sub doit
{
  my ($cols) = @_;

  my $COLS = join(', ', @$cols);
  my $sql = "SELECT $COLS FROM t AS a, t AS b WHERE a.name = 'b' AND b
+.name = 'c' AND a.x = b.x";
  my $sth = $dbh->prepare($sql);

  my $rv = $sth->execute();

  my $results = $sth->fetchall_arrayref({});

  print Data::Dumper->new([ $sql, $results ], [qw( $sql $results)])->D
+ump(), "\n";
}


init();

doit(['*']);
doit(['a.*', 'b.*']);
doit(['a.id', 'a.x', 'a.name', 'b.id', 'b.x', 'b.name']);

1;

[download]

Replies are listed 'Best First'.
Re: DBI table AND column names in fetchall_arrayref({})?
by gmax (Abbot) on Feb 02, 2002 at 09:12 UTC
    I am afraid that this is standard MySQL behaviour. The DBI will report whatever it gets from the DBD driver, and in this case MySQL does not report the table name.
    mysql> describe test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  |      | PRI | NULL    | auto_increment |
| title | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+----+--------+
| id | title  |
+----+--------+
|  1 | first  |
|  2 | second |
+----+--------+
2 rows in set (0.00 sec)

mysql> select a.id, a.title, b.id, b.title from test a, test b where a
+.id=b.id;
+----+--------+----+--------+
| id | title  | id | title  |
+----+--------+----+--------+
|  1 | first  |  1 | first  |
|  2 | second |  2 | second |
+----+--------+----+--------+
2 rows in set (0.00 sec)

    [download]
    As you can see, querying from the normal MySQL client, the server is returning the same name for both fields. Thus, DBI will get only one of the two columns.
    From DBI POD:
    If more than one field has the same name, there will only be one entry in the returned hash for those fields.

    However, if you ask esplicitly, you get the distinction.
    I advise you to differenciate ambiguous names ALWAYS, no matter how smart the database driver seems to be .
    mysql> select a.id as a_id, a.title as a_title, b.id as b_id, b.title 
+as b_title from test a inner join test2 b using(id);
+------+---------+------+---------+
| a_id | a_title | b_id | b_title |
+------+---------+------+---------+
|    1 | first   |    1 | first   |
|    2 | second  |    2 | second  |
+------+---------+------+---------+
2 rows in set (0.00 sec)

    [download]
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
      I figured that either MySQL was not passing table names back through the DBI or DBI has no interface for the table name of columns. I agree it would be more portable to use "SELECT a.id as a_id ..." but it would have been nice to use "SELECT * ..." and keep the metadata in the DB. My SELECT stmts are dynamically generated; i'll just have keep the metadata around to generate the "a.id as a_id" aliases. Thanks.
[reply]
Re: DBI table AND column names in fetchall_arrayref({})?
by Ryszard (Priest) on Feb 02, 2002 at 09:06 UTC

    Its not really a perl question, more the way your DB engine works.

    The only method i can think of is using a subquery, and fudging it that way, altho' you need the table structure to support what you need.

    I guess it doesnt exist, because, you already must know the table_name you want in order for you to run a query against it. For the db engine to supply the information to you that you already have would be double handling.

[reply]

Back to Seekers of Perl Wisdom