Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

DBI returning mysql columns in strange order.

by neilwatson (Priest)
on Oct 09, 2003 at 21:28 UTC ( [id://298110]=perlquestion: print w/replies, xml ) Need Help??

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

Consider this code:
#get everything from the table my $statement = "select * from $ref"; my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement +: $dbh->errstr"; $sth->execute or die print "Couldn't execute statement: $dbh->errstr"; while ($ref = $sth->fetchrow_hashref){ # must be the first row # print headers if ($count == 1){ print "\t<tr>\n"; # get column names foreach $col (keys %{$ref}){ # do not list id field if ($col eq "id") { next; } # save column names for later push @cols, $col; $col = ucfirst $col; print "\t\t<th>$col</th>\n"; } print "\t</tr>\n"; } print "\t<tr>\n"; #using column names (key) print hash value foreach $col (@cols){ print "\t\t<td>"; print $ref->{$col}; print "</td>\n"; } $count++; print "\t</tr>\n"; } print "</table>\n";

If I use this code for two mysql tables:

  1. Columns: id, example, points
  2. Columns: id, condition, cshift

For the first table, our perl code displays the columns as I would expect:
Example Points

The second table displays unexpectedly:
Cshift Condition

Yet, if I run a the same query from inside the mysql program, the columns are displayed in the correct order. What is wrong?

Neil Watson
watson-wilson.ca

Replies are listed 'Best First'.
Re: DBI returning mysql columns in strange order.
by runrig (Abbot) on Oct 09, 2003 at 21:42 UTC
    There is no order to keys of a hash array (unless you think that random == ordered ). If you want order, you can get it by using the NAME or NAME_lc attribute of the statement handle to get the order of selected columns, e.g.:
    $sth->execute; my @columns = @{ $sth->{NAME_lc} }; my %row; $sth->bind_columns(\@row{@columns}); while ($sth->fetch) { for (@columns) { print "$_ = $row{$_}\n"; } }
    Updated.
Re: DBI returning mysql columns in strange order.
by sandfly (Beadle) on Oct 09, 2003 at 21:41 UTC
    Each row is returned in a hash reference. The columns are ordered according to how their names hash, which pseudo-random.

    I am no DBI expert, but I suspect there will be another way to retrieve the results, which preserves the column order.

Re: DBI returning mysql columns in strange order.
by shenme (Priest) on Oct 09, 2003 at 21:42 UTC
    Keys of a hash are not returned in any particular order.   Try
    # get column names foreach $col (sort keys %{$ref}){
    to get your keys in alpha sorted order.   If you want them in another order, you will have to create an array to define the order you want and do something like
    # get column names my @col_order = qw( Condition Cshift ); foreach $col (@col_order){
    but then you might run into problems with getting the casing of your names correct.

    What you _really_ want to do is find out the order of your fields directly from the DB and   ...   I don't remember how to do that just now. :-(   Check the docs and you'll find it I'm sure.

Re: DBI returning mysql columns in strange order.
by diotalevi (Canon) on Oct 09, 2003 at 21:45 UTC

    The return result of keys %{$ref} is ordered semi-randomly. At 5.8.1 the ordering is even more random. If you want to see your columns in a specific order then you have to either provide that order yourself or sort them.

Re: DBI returning mysql columns in strange order.
by antirice (Priest) on Oct 09, 2003 at 21:54 UTC

    Hashes are unordered. keys does not necessarily return the keys in the same order as they were entered. If you want the keys in the order that they came from the database, you can change:

    while ($ref = $sth->fetchrow_hashref){ ... foreach $col (keys %$ref) {

    to

    while ($ref = $sth->fetchrow_hashref("NAME_lc")){ ... foreach $col (@{$sth->{"NAME_lc"})

    Hope this helps.

    Note: $sth->fetchrow_hashref defaults to "NAME" and you can use it in the foreach statement if you wish. I use NAME_lc for hysterical reasons about which I was informed during my introduction to DBI.

    antirice    
    The first rule of Perl club is - use Perl
    The
    ith rule of Perl club is - follow rule i - 1 for i > 1

Re: DBI returning mysql columns in strange order.
by jdtoronto (Prior) on Oct 09, 2003 at 22:11 UTC
    And ask yourself why is the order important?

    If you get them back in a HASHREF then you can deal with them in whatever order you like. You cannot rely on any order when you are using HASHes. Nor should you!

    jdtoronto

Re: DBI returning mysql columns in strange order.
by dragonchild (Archbishop) on Oct 09, 2003 at 22:29 UTC
    I am shocked that no-one suggested the obvious solution, which also happens to be the better solution in the long run.

    Change the SELECT statement to specify what you're looking for!

    This would require some rewriting and quite a lot of rethinking the design, but it would be more maintainable and extensible. If you want, I can provide an example of how I would write this snippet using structured design principles.

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

      Yes, that is an obvious way, but even then not necessarilly if you retrieve using a HASHREF.
      my $select = qq~SELECT field1 field2 field3 FROM $ref;~; my $sth = $dbh->prepare( $select ); $sth->execute(); my ($field1, $field2, $field3) = $sth->fetchrow_array;
      Will get things EXACTLY where you want them, so would
      my @inarray = $sth->fetchrow_array();
      Or the alternative of having DBI return and arrayref.

      But is the idea fo specifying the specific fields extensible and maintainable? My logic would be that if the table structure changes then wildcard of fields does not need to be changed. If you use the HASHREF methods of collecting the data from DBI then you only need be concerned about the actual fields when you look into the HASH for the values.

      Of course I stand to be corrected on this, but I would think that having to change the code as well as the SQL is less maintainable than just having to change the code.

      jdtoronto

      Just like him: All opinions are purely mine and all code is untested, unless otherwise specified

        Please stand corrected. *grins*
        sub display_table { my ($dbh, $table_name, $columns, $is_ordered) = @_; my $sql = 'SELECT '; $sql .= join ',', @$columns; $sql .= "\n FROM $table_name"; $sql .= "\n ORDER BY " . join(',', 1 .. @$columns) if $is_ordered; print "$sql\n"; my $sth = $dbh->prepare_cached($sql) or die "Cannot prepare '$sql':\n" . $dbh->errstr; $sth->execute or die "Cannot execute '$sql':n" . $dbh->errstr; my $values = $sth->fetchall_arrayref; $sth->finish; return $values; }

        ------
        We are the carpenters and bricklayers of the Information Age.

        The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

        Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

      Indeed. select * from ... is really bad form (although expedient when you are in a hurry, I'll admit).

      That said the fundamental problem of the OP's code, as has been noted in one of the previous responses, is the reliance on the order of hash keys - I'd like to stress that one should retrieve column names via $sth->{NAME} (or the NAME_lc/NAME_uc variations) as that will correctly list all the column names in the proper order.

      Michael

        There is a method to my madness. I choose select * from ... because the code is meant to work on a collection of tables whose columns are all different.

        My application involved having a group of small tables. Then I would have this code query and display any table that was selected. I'm trying to be lazyTM

        Neil Watson
        watson-wilson.ca

A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://298110]
Approved by Paladin
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (6)
As of 2024-03-28 16:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found