Phoenix.pm: Addition to MySQL DBD

[Webmaster]

Well, it's a slow Columbus day, so I'd thought I'd share this little bit of
MySQL code.

DBI POD describes a method called 'selectcol_array' which the MySQL DBD does
not support as it supposedly should.

The 'selectcol_array' method is a very useful one, as you can get an array
of column values from a select statement in one fell swoop.

It's a good example of adding a little piece of your own code to an already
existing class.

Here is the code:

package DBI::db;  # This is the class that a MySQL database handle belongs
to (which calls this method)

 # The method name that is supposed to be there according to DBI docs, but
isn't (that I know of)
sub selectcol_array {
        # Use an existing DBI::db method.
        #  NOTE: @_ automatically is sent to sub if you leave off '()'.
        # i.e. &selectall_arrayref() would not work.
    my $arrayref = &selectall_arrayref;
    my @return = ($arrayref)?(map { $$_[0] } @$arrayref):();  # The expected
return is a list... unless...
        # The behavior of selectrow_array is to send the first item in the
list if called in a scalar context.
        # So, lets make selectcol_array behave the same, Even though in
scalar context, the two now do the same thing.
        # ed. that makes very efficient calls to selectrow_array if you only
want one value,
        #         i.e. my $email = $DBH->selectrow_array("select email from
users where uid=3");
    return (wantarray)?@return:$return[0]
}

# SYNOPSIS

package main;

$DBH = DBI->connect('DBI:mysql:azwebs', 'azwebs', 'acompletelybogupassword')
|| die "unable to connect";

# to get a list of email addresses that have a certain preference selected
from a prefs column...

for ($DBH->selectcol_array( "select email from users where
find_in_set('send_updates', prefs)"))  {
        ICM::Utilities->email_update_message( $_ );
}

Any questions on this?  Couple of neat MySQL things here...

Happy Columbus Day (That 'eeeevil' European!)

Tim